August 23, 2010 at 5:05 pm
Trying to figure out my first pivot query.
I have a table that has two rows per site. I need to combine Meal 1 and Meal 2 to one row. Basically Meal 1 FreeCnt, Meal 1 Reduced and Meal 1 Fullpay go together and then Meal 2 FreeCnt, Meal 2 Reduced and Meal 2 FullPay go together.
So the end result should be like this.
SiteID, Servdate, Meal 1 FreeCnt, Meal 1 Reduced, Meal 1FullPay, Meal 1 Total, Meal 2 FreeCnt, Meal 2 Reduced, Meal 2 FullPay, Meal 2 Total
August 23, 2010 at 6:22 pm
It helps to give a simple create table script with some sample data....here is one with a non-pivot method of solving this. I know just enough about pivots to not know how to pivot multiple columns like this, but I can make a sample table so someone else can show you the pivot. 😀
BEGIN TRAN
Create Table #Meals
(SiteID int,
ServDate datetime,
Meal int,
FreeCnt int,
Reduced int,
FullPay int
)
Go
Insert Into #Meals
(SiteID, ServDate, Meal, FreeCnt, Reduced, FullPay)
Select 1, '8/23/2010',1,59,3,9 Union ALL
Select 1, '8/23/2010',2,731,57,103 Union ALL
Select 2, '8/23/2010',1,59,3,9 Union ALL
Select 2, '8/23/2010',2,731,57,103 Union ALL
Select 3, '8/23/2010',1,59,3,9 Union ALL
Select 3, '8/23/2010',2,731,57,103
Go
Select * from #Meals
Select M1.SiteID, M1.ServDate,
M1.FreeCnt as Meal1FreeCnt, M1.Reduced as Meal1Reduced, M1.FullPay as Meal1FullPay,
M1.FreeCnt + M1.reduced + M1.FullPay as Meal1Total,
M2.FreeCnt as Meal2FreeCnt, M2.Reduced as Meal2Reduced, M2.FullPay as Meal2FullPay,
M2.FreeCnt + M2.reduced + M2.FullPay as Meal2Total
From #Meals as M1
inner join #Meals as M2 on M1.SiteID = M2.SiteID and M1.Meal = 1 and M2.Meal = 2
go
Drop Table #Meals
ROLLBACK
August 24, 2010 at 2:25 am
Being a multi-column PIVOT, this a bit tricky. Here I first shape the data (UNPIVOT) using CROSS APPLY and then PIVOT back on the FR column created in the CTE block
BTW thanks Wayne for supplying the meals:-)
;WITH cte1 AS
(
SELECT *
FROM #Meals WHERE Meal = 1
)
,
cte2 AS
(
SELECT *
FROM #Meals WHERE Meal = 2
)
,
cte3 AS
(
SELECT * FROM cte1
CROSS APPLY
(
SELECT 1, FreeCnt UNION ALL
SELECT 2, Reduced UNION ALL
SELECT 3, FullPay
) AS Y (ID, FR)
UNION ALL SELECT * FROM cte2
CROSS APPLY
(
SELECT 4, FreeCnt UNION ALL
SELECT 5, Reduced UNION ALL
SELECT 6, FullPay
) AS Z (ID, FR)
)
SELECT SiteID, ServDate, [1] AS Meal1FreeCnt, [2] AS Meal1Reduced, [3] AS Meal1FullPay, [1] + [2] + [3] AS Meal1Total, [4] AS Meal2FreeCnt, [5] AS Meal2Reduced, [6] AS Meal2FullPay, [4] + [5] + [6] AS Meal2Total
FROM (SELECT SiteID, ServDate, ID, FR FROM cte3) AS Q
PIVOT (MAX(FR) FOR ID IN ([1],[2],[3],[4],[5],[6])) AS R
August 24, 2010 at 4:53 am
This solution worked great. With one exception. The Total columns it is concatenating the three fields instead of adding them together. I tried to fix, but it is not working.
August 24, 2010 at 5:02 am
Could you supply the CREATE TABLE script for the table you are working on
August 24, 2010 at 5:11 am
Why don't you use the "old-fashioned" CASE statement?
SELECT
SiteID,
ServDate,
SUM(CASE WHEN Meal=1 THEN FreeCnt ELSE 0 END) as Meal1FreeCnt,
SUM(CASE WHEN Meal=1 THEN Reduced ELSE 0 END) as Meal1Reduced,
SUM(CASE WHEN Meal=1 THEN FullPay ELSE 0 END) as Meal1FullPay,
SUM(CASE WHEN Meal=1 THEN FreeCnt+Reduced+FullPay ELSE 0 END) as Meal1Total,
SUM(CASE WHEN Meal=2 THEN FreeCnt ELSE 0 END) as Meal2FreeCnt,
SUM(CASE WHEN Meal=2 THEN Reduced ELSE 0 END) as Meal2Reduced,
SUM(CASE WHEN Meal=2 THEN FullPay ELSE 0 END) as Meal2FullPay,
SUM(CASE WHEN Meal=2 THEN FreeCnt+Reduced+FullPay ELSE 0 END) as Meal2Total
FROM #Meals
GROUP BY SiteID,ServDate
At least you should give it a try and compare performance. I guess it's worth it... 😉
August 24, 2010 at 7:05 am
gbargsley (8/24/2010)
This solution worked great. With one exception. The Total columns it is concatenating the three fields instead of adding them together. I tried to fix, but it is not working.
If it's concatenating the three fields, are they stored as char/varchar, or are they stored as some sort of number? If they are stored as text, convert them to numbers to add them together.
Cast(M1.FreeCnt as Integer) + Cast(M1.reduced as Integer) + cast(M1.FullPay as Integer) as Meal1Total
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply