• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

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
0
rwheeler23
Asked:
rwheeler23
  • 4
  • 4
1 Solution
 
Surendra NathTechnology LeadCommented:
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

0
 
rwheeler23Author Commented:
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'.
0
 
Surendra NathTechnology LeadCommented:
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

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
rwheeler23Author Commented:
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'.
0
 
Surendra NathTechnology LeadCommented:
My Mistake used improper column name, use the below

;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 MemberNumberId as contributerID,[2009],[2010],[2011],[2012]
FROM 
(
  SELECT * FROM C
) as p
PIVOT
(
  SUM(Amount) FOR YearContrib IN ([2009],[2010],[2011],[2012])
) as pvt 
order by 1

Open in new window

0
 
rwheeler23Author Commented:
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?
0
 
Surendra NathTechnology LeadCommented:
it is called Common Table Expression, commonly abbreviated as CTE's... they can be used in various number of places...
0
 
rwheeler23Author Commented:
Thank you so very much! Very clear and concise solution and follow up was excellent.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now