Pivot Query

Question

Pivot Query

gbargsley

SSCommitted

Points: 1514
More actions
August 23, 2010 at 5:05 pm

#144778

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

wayne.mcdaniel SSCommitted Points: 1546 More actions · Answer 1

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

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 2

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

gbargsley SSCommitted Points: 1514 More actions · Answer 3

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.

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 4

Could you supply the CREATE TABLE script for the table you are working on

LutzM SSC Guru Points: 107049 More actions · Answer 5

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... 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

wayne.mcdaniel SSCommitted Points: 1546 More actions · Answer 6

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