Link to home
Create AccountLog in
Avatar of rwheeler23
rwheeler23Flag for United States of America

asked on

Pivot SQL Query

This query list each contributor by year. What I would like is to see

Contributor   Year 1  Year 2  Year 3 Year 4

For example:
Contributor   Year 1  Year 2  Year 3 Year 4
100                 $100      $200   $300  $400
200                   $50      $100   $150  $200


SELECT year(t2.date) as YearContrib,t2.MemberNumberId,sum(coalesce([Amount],0)) as Amount
FROM [Contribution Details] t1
inner join [Contribution Headers] t2 on t1.ContribHeaderId=t2.ContribNumberId
group by t2.MemberNumberId,year(t2.date)      
order by MemberNumberId,year(t2.date)

How do I restructure this query to accomplish this? I am using SQL Server 2012
Avatar of Surendra Nath
Surendra Nath
Flag of India image

as long as you have only 4 years in the selection criteria then you can use the below approach

;with C AS
(
SELECT year(t2.date) as YearContrib,t2.MemberNumberId,sum(coalesce([Amount],0)) as Amount
FROM [Contribution Details] t1
inner join [Contribution Headers] t2 on t1.ContribHeaderId=t2.ContribNumberId
group by t2.MemberNumberId,year(t2.date)      
)
select MemberNumID as contributerID,[2009],[2010],[2011],[2012]
FROM 
(
  SELECT * FROM C
)
PIVOT
(
  SUM(Amount) FOR MemberNumID IN ([2009],[2010],[2011],[2012])
) as pvt 
order by 1

Open in new window


incase if the number of years are dynamic and you don't know what they will be then you have to use a dynamic PIVOT as we do below



DECLARE @SQL VARCHAR(3000)
DECLARE @SQ VARCHAR(4) = ''''
;with C AS
(
SELECT year(t2.date) as YearContrib,t2.MemberNumberId,sum(coalesce([Amount],0)) as Amount
FROM [Contribution Details] t1
inner join [Contribution Headers] t2 on t1.ContribHeaderId=t2.ContribNumberId
group by t2.MemberNumberId,year(t2.date)      
), D AS
(
 SELECT DISTINCT YearContrib FROM C
), E AS
(
SELECT STUFF( (SELECT ',' + '[' + YearContrib + ']'  as Y FROM D FOR XML PATH ('')),1,1,'')
)
SELECT @SQL = 
'
;with C AS
(
SELECT year(t2.date) as YearContrib,t2.MemberNumberId,sum(coalesce([Amount],0)) as Amount
FROM [Contribution Details] t1
inner join [Contribution Headers] t2 on t1.ContribHeaderId=t2.ContribNumberId
group by t2.MemberNumberId,year(t2.date)      
)
' + 
'
select MemberNumID as contributerID, ' + (SELECT Y FROM D) + 
FROM 
(
  SELECT * FROM C
)
PIVOT
(
  SUM(Amount) FOR MemberNumID IN ( ' + (SELECT Y FROM D) + ')
) as pvt 
order by 1 '

Open in new window

Avatar of rwheeler23

ASKER

The query give me this message:

Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'PIVOT'.

And the second one gives me:

Msg 156, Level 15, State 1, Line 28
Incorrect syntax near the keyword 'FROM'.
Msg 156, Level 15, State 1, Line 32
Incorrect syntax near the keyword 'PIVOT'.
check this out

;with C AS
(
SELECT year(t2.date) as YearContrib,t2.MemberNumberId,sum(coalesce([Amount],0)) as Amount
FROM [Contribution Details] t1
inner join [Contribution Headers] t2 on t1.ContribHeaderId=t2.ContribNumberId
group by t2.MemberNumberId,year(t2.date)      
)
select MemberNumID as contributerID,[2009],[2010],[2011],[2012]
FROM 
(
  SELECT * FROM C
) as p
PIVOT
(
  SUM(Amount) FOR MemberNumID IN ([2009],[2010],[2011],[2012])
) as pvt 
order by 1

Open in new window

Now I get these:

Msg 207, Level 16, State 1, Line 15
Invalid column name 'MemberNumID'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'MemberNumID'.
ASKER CERTIFIED SOLUTION
Avatar of Surendra Nath
Surendra Nath
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
This is brilliant. Thank you so much. I have seen this construct before but never fully understood it.

May I asked what does the

;with C AS

do?
it is called Common Table Expression, commonly abbreviated as CTE's... they can be used in various number of places...
Thank you so very much! Very clear and concise solution and follow up was excellent.