Asked by:
How to dynamically create multiple Pivot join

Is it possible for me to do the following in dynamic pivot? The code below basically in the first select put the 'result' value for each month into the 'Value' field in the temp table #ValueBucket. The select statement does the same thing, except it does it for the 'rating' field value. The next two select statements follow the same logic except it populates the temp table with the year to date Result and Rating values.
I populated this temp table with these fields values, so that down in my pivot I would be able to just pivot on one field(Value) versus having to join on multiple pivot. This seems to be working fine for me now. I was just interested in if it was possible for me to code the pivot dynamically?
Current Code:
BEGIN   Get Result Values   Select EmployeeID ,Employee ,EmpGroupID ,PA ,EmpLevel ,ObjectiveName as Objective ,Objectiveid as Objectiveid ,Weighting ,ReportingYear ,CASE max(MONTH(r.startdate)) WHEN 1 THEN 'January_Result' WHEN 2 THEN 'February_Result' WHEN 3 THEN 'March_Result' WHEN 4 THEN 'April_Result' WHEN 5 THEN 'May_Result' WHEN 6 THEN 'June_Result' WHEN 7 THEN 'July_Result' WHEN 8 THEN 'August_Result' WHEN 9 THEN 'September_Result' WHEN 10 THEN 'October_Result' WHEN 11 THEN 'November_Result' WHEN 12 THEN 'December_Result' END as [StartMonth] ,MAX([Result]) as Value into #ValueBucket From #tblResults R INNER JOIN @Emphist h ON r.Empgroupid = h.Groupid LEFT OUTER JOIN Config.tblRatingDescription rt ON r.Rating = rt.RatingID LEFT OUTER JOIN Config.tblRatingDescription rtovr ON r.RatingOverride = rtovr.RatingID WHERE r.startdate BETWEEN '20120101' AND '20120501' AND r.datetype IN ('M','Y') GROUP BY EmployeeID, Employee, EmpGroupID, PA, EmpLevel, ObjectiveName, Objectiveid, ReportingYear, Weighting   Get rating values   Insert Into #ValueBucket(EmployeeID,Employee,EmpGroupID,PA,EmpLevel,Objective,Objectiveid,Weighting,ReportingYear,StartMonth,Value) Select EmployeeID ,Employee ,EmpGroupID ,PA ,EmpLevel ,ObjectiveName as Objective ,Objectiveid as Objectiveid ,Weighting ,ReportingYear ,CASE max(MONTH(r.startdate)) WHEN 1 THEN 'January_Rating' WHEN 2 THEN 'February_Rating' WHEN 3 THEN 'March_Rating' WHEN 4 THEN 'April_Rating' WHEN 5 THEN 'May_Rating' WHEN 6 THEN 'June_Rating' WHEN 7 THEN 'July_Rating' WHEN 8 THEN 'August_Rating' WHEN 9 THEN 'September_Rating' WHEN 10 THEN 'October_Rating' WHEN 11 THEN 'November_Rating' WHEN 12 THEN 'December_Rating' END as [StartMonth] ,MAX([Rating]) as Value From #tblResults R INNER JOIN @Emphist h ON r.Empgroupid = h.Groupid LEFT OUTER JOIN Config.tblRatingDescription rt ON r.Rating = rt.RatingID LEFT OUTER JOIN Config.tblRatingDescription rtovr ON r.RatingOverride = rtovr.RatingID WHERE r.startdate BETWEEN '20120101' AND '20120501' AND r.datetype IN ('M','Y') GROUP BY EmployeeID, Employee, EmpGroupID, PA, EmpLevel, ObjectiveName, Objectiveid, ReportingYear, Weighting   Get Result YTD values   Insert Into #ValueBucket(EmployeeID,Employee,EmpGroupID,PA,EmpLevel,Objective,Objectiveid,Weighting,ReportingYear,StartMonth,Value) Select EmployeeID ,Employee ,EmpGroupID ,PA ,EmpLevel ,ObjectiveName as Objective ,Objectiveid as Objectiveid ,Weighting ,ReportingYear ,max(Case When r.DateType = 'Y' and Month(r.StartDate) = 1 AND r.result IS NOT NULL Then 'Result_YTD_13' end) as [StartMonth] ,max(Case When r.DateType = 'Y' and Month(r.StartDate) = 1 AND r.result IS NOT NULL Then r.Result When r.DateType = 'Y' and Month(r.StartDate) = 1 AND r.result IS NULL AND r.rating is NOT NULL THEN COALESCE(rtovr.ratingShortDesc,rt.ratingShortDesc) Else null END) as Value From #tblResults R INNER JOIN @Emphist h ON r.Empgroupid = h.Groupid LEFT OUTER JOIN Config.tblRatingDescription rt ON r.Rating = rt.RatingID LEFT OUTER JOIN Config.tblRatingDescription rtovr ON r.RatingOverride = rtovr.RatingID WHERE r.startdate BETWEEN '20120101' AND '20120501' AND r.datetype IN ('M','Y') GROUP BY EmployeeID, Employee, EmpGroupID, PA, EmpLevel, ObjectiveName, Objectiveid, ReportingYear, Weighting   Get Rating YTD values   Insert Into #ValueBucket(EmployeeID,Employee,EmpGroupID,PA,EmpLevel,Objective,Objectiveid,Weighting,ReportingYear,StartMonth,Value) Select EmployeeID ,Employee ,EmpGroupID ,PA ,EmpLevel ,ObjectiveName as Objective ,Objectiveid as Objectiveid ,Weighting ,ReportingYear ,max(Case When r.DateType = 'Y' and Month(r.StartDate) = 1 Then 'Rating_YTD_13' end) as [StartMonth] ,max(Case When r.DateType = 'Y' and Month(r.StartDate) = 1 Then dbo.udfGetRating(case when r.RatingOverride is null then r.Rating else r.RatingOverride end ) + '' + CAST(UserOverride AS CHAR(1)) + '' + rt.ratingShortDesc Else null END ) as Value From #tblResults R INNER JOIN @Emphist h ON r.Empgroupid = h.Groupid LEFT OUTER JOIN Config.tblRatingDescription rt ON r.Rating = rt.RatingID LEFT OUTER JOIN Config.tblRatingDescription rtovr ON r.RatingOverride = rtovr.RatingID WHERE r.startdate BETWEEN '20120101' AND '20120501' AND r.datetype IN ('M','Y') GROUP BY EmployeeID, Employee, EmpGroupID, PA, EmpLevel, ObjectiveName, Objectiveid, ReportingYear, Weighting   Pivot   Select * From ( Select v.EmployeeID ,v.Employee ,v.EmpGroupID ,v.PA ,v.EmpLevel ,v.Objective as Objective ,v.Objectiveid as Objectiveid ,v.Weighting ,v.ReportingYear ,v.StartMonth ,v.Value From #ValueBucket v ) TableDate PIVOT ( MAX(Value) FOR [StartMonth] in(January_Result,January_Rating,February_Result,February_Rating,March_Result,March_Rating,April_Result,April_Rating, May_Result,May_Rating,June_Result,June_Rating,July_Result,July_Rating,August_Result,August_Rating,September_Result,September_Rating, October_Result,October_Rating,November_Result,November_Rating,December_Result, December_Rating,Result_YTD_13,Rating_YTD_13 ) )PivotTable END
Question
All replies

If you want to pivot multiple pivots, it's easier (IMO) to just use case statements rather than pivot. So, the following is a simplified example that pivots Result and Rating over three month (Jan, Feb, and Mar). Just extend it to all the other months.
Create Table #Test(EmployeeID int, EmployeeGroupID int, Month int, Result int, Rating int); Insert #Test(EmployeeID, EmployeeGroupID, Month, Result, Rating) Values (1,1,1,25,30), (1,1,1,215,10), (1,1,2,125,230), (1,1,3,2,17), (1,2,1,325,330), (1,2,2,425,430), (1,2,3,525,530); Select t.EmployeeID, t.EmployeeGroupID, Max(Case When t.Month = 1 Then t.Result End) As JanuaryResult, Max(Case When t.Month = 1 Then t.Result End) As JanuaryRating, Max(Case When t.Month = 2 Then t.Result End) As FebruaryResult, Max(Case When t.Month = 2 Then t.Result End) As FebruaryRating, Max(Case When t.Month = 3 Then t.Result End) As MarchResult, Max(Case When t.Month = 3 Then t.Result End) As MarchRating From #Test t Group By t.EmployeeID, t.EmployeeGroupID Order By t.EmployeeID, t.EmployeeGroupID; go Drop Table #Test;
Tom 
Thanks for taking the time to look over my code Tom I really appreicate it. But currently that is how the work code I have in production is. It uses CASE statements. But I wanted to clean the code up and rid of all those CASE statement so thats why I elected to gowith the above. I was just wondering if I could dynamically build out that pivot though.
My current CASE statements:
Select EmployeeID, Employee, Datetype, EmpGroupID, PA, EmpLevel, ObjectiveName as Objective, Objectiveid AS Objectiveid, Weighting, ReportingYear, max(Case When r.DateType = 'M' and Month(r.StartDate) = 1 AND r.result IS NOT NULL Then r.Result When r.DateType = 'M' and Month(r.StartDate) = 1 AND r.result IS NULL AND r.rating is NOT NULL THEN COALESCE(rtovr.ratingShortDesc,rt.ratingShortDesc) Else null END) as Value_January_1, MAX(Case When r.DateType = 'M' and Month(r.StartDate) = 1 Then (case when r.RatingOverride is null then rt.RatingColor else rtovr.RatingColor end) Then dbo.udfGetRating(case when r.RatingOverride is null then r.Rating else r.RatingOverride end) + '' + CAST(UserOverride AS CHAR(1)) + '' + rt.ratingShortDesc Else null END) as Rating_January_1, max(Case When r.DateType = 'M' and Month(r.StartDate) = 2 AND r.result IS NOT NULL Then r.Result When r.DateType = 'M' and Month(r.StartDate) = 2 AND r.result IS NULL AND r.rating is NOT NULL THEN COALESCE(rtovr.ratingShortDesc,rt.ratingShortDesc) Else null END) as Value_February_2, MAX(Case When r.DateType = 'M' and Month(r.StartDate) = 2 Then dbo.udfGetRating(case when r.RatingOverride is null then r.Rating else r.RatingOverride end) + '' + CAST(UserOverride AS CHAR(1)) + '' + rt.ratingShortDesc Else null END) as Rating_February_2, max(Case When r.DateType = 'M' and Month(r.StartDate) = 3 AND r.result IS NOT NULL Then r.Result When r.DateType = 'M' and Month(r.StartDate) = 3 AND r.result IS NULL AND r.rating is NOT NULL THEN COALESCE(rtovr.ratingShortDesc,rt.ratingShortDesc) Else null END) as Value_March_3, MAX(Case When r.DateType = 'M' and Month(r.StartDate) = 3 Then dbo.udfGetRating(case when r.RatingOverride is null then r.Rating else r.RatingOverride end) + '' + CAST(UserOverride AS CHAR(1)) + '' + rt.ratingShortDesc Else null END) as Rating_March_3, max(Case When r.DateType = 'M' and Month(r.StartDate) = 4 AND r.result IS NOT NULL Then r.Result When r.DateType = 'M' and Month(r.StartDate) = 4 AND r.result IS NULL AND r.rating is NOT NULL THEN COALESCE(rtovr.ratingShortDesc,rt.ratingShortDesc) Else null END) as Value_April_4, MAX(Case When r.DateType = 'M' and Month(r.StartDate) = 4 Then dbo.udfGetRating(case when r.RatingOverride is null then r.Rating else r.RatingOverride end) + '' + CAST(UserOverride AS CHAR(1)) + '' + rt.ratingShortDesc Else null END) as Rating_April_4, max(Case When r.DateType = 'M' and Month(r.StartDate) = 5 AND r.result IS NOT NULL Then r.Result When r.DateType = 'M' and Month(r.StartDate) = 5 AND r.result IS NULL AND r.rating is NOT NULL THEN COALESCE(rtovr.ratingShortDesc,rt.ratingShortDesc) Else null END) as Value_May_5, MAX(Case When r.DateType = 'M' and Month(r.StartDate) = 5 Then dbo.udfGetRating(case when r.RatingOverride is null then r.Rating else r.RatingOverride end) + '' + CAST(UserOverride AS CHAR(1)) + '' + rt.ratingShortDesc Else null END) as Rating_May_5, max(Case When r.DateType = 'M' and Month(r.StartDate) = 6 AND r.result IS NOT NULL Then r.Result When r.DateType = 'M' and Month(r.StartDate) = 6 AND r.result IS NULL AND r.rating is NOT NULL THEN COALESCE(rtovr.ratingShortDesc,rt.ratingShortDesc) Else null END) as Value_June_6, MAX(Case When r.DateType = 'M' and Month(r.StartDate) = 6 Then dbo.udfGetRating(case when r.RatingOverride is null then r.Rating else r.RatingOverride end) + '' + CAST(UserOverride AS CHAR(1)) + '' + rt.ratingShortDesc Else null END) as Rating_June_6, max(Case When r.DateType = 'M' and Month(r.StartDate) = 7 AND r.result IS NOT NULL Then r.Result When r.DateType = 'M' and Month(r.StartDate) = 7 AND r.result IS NULL AND r.rating is NOT NULL THEN COALESCE(rtovr.ratingShortDesc,rt.ratingShortDesc) Else null END) as Value_July_7, MAX(Case When r.DateType = 'M' and Month(r.StartDate) = 7 Then dbo.udfGetRating(case when r.RatingOverride is null then r.Rating else r.RatingOverride end) + '' + CAST(UserOverride AS CHAR(1)) + '' + rt.ratingShortDesc Else null END) as Rating_July_7, max(Case When r.DateType = 'M' and Month(r.StartDate) = 8 AND r.result IS NOT NULL Then r.Result When r.DateType = 'M' and Month(r.StartDate) = 8 AND r.result IS NULL AND r.rating is NOT NULL THEN COALESCE(rtovr.ratingShortDesc,rt.ratingShortDesc) Else null END) as Value_August_8, MAX(Case When r.DateType = 'M' and Month(r.StartDate) = 8 Then dbo.udfGetRating(case when r.RatingOverride is null then r.Rating else r.RatingOverride end) + '' + CAST(UserOverride AS CHAR(1)) + '' + rt.ratingShortDesc Else null END) as Rating_August_8, max(Case When r.DateType = 'M' and Month(r.StartDate) = 9 AND r.result IS NOT NULL Then r.Result When r.DateType = 'M' and Month(r.StartDate) = 9 AND r.result IS NULL AND r.rating is NOT NULL THEN COALESCE(rtovr.ratingShortDesc,rt.ratingShortDesc) Else null END) as Value_September_9, MAX(Case When r.DateType = 'M' and Month(r.StartDate) = 9 Then dbo.udfGetRating(case when r.RatingOverride is null then r.Rating else r.RatingOverride end) + '' + CAST(UserOverride AS CHAR(1)) + '' + rt.ratingShortDesc Else null END) as Rating_September_9, max(Case When r.DateType = 'M' and Month(r.StartDate) = 10 AND r.result IS NOT NULL Then r.Result When r.DateType = 'M' and Month(r.StartDate) = 10 AND r.result IS NULL AND r.rating is NOT NULL THEN COALESCE(rtovr.ratingShortDesc,rt.ratingShortDesc) Else null END) as Value_October_10, MAX(Case When r.DateType = 'M' and Month(r.StartDate) = 10 Then dbo.udfGetRating(case when r.RatingOverride is null then r.Rating else r.RatingOverride end) + '' + CAST(UserOverride AS CHAR(1)) + '' + rt.ratingShortDesc Else null END) as Rating_October_10, max(Case When r.DateType = 'M' and Month(r.StartDate) = 11 AND r.result IS NOT NULL Then r.Result When r.DateType = 'M' and Month(r.StartDate) = 11 AND r.result IS NULL AND r.rating is NOT NULL THEN COALESCE(rtovr.ratingShortDesc,rt.ratingShortDesc) Else null END) as Value_November_11, MAX(Case When r.DateType = 'M' and Month(r.StartDate) = 11 Then dbo.udfGetRating(case when r.RatingOverride is null then r.Rating else r.RatingOverride end) + '' + CAST(UserOverride AS CHAR(1)) + '' + rt.ratingShortDesc Else null END) as Rating_November_11, max(Case When r.DateType = 'M' and Month(r.StartDate) = 12 AND r.result IS NOT NULL Then r.Result When r.DateType = 'M' and Month(r.StartDate) = 12 AND r.result IS NULL AND r.rating is NOT NULL THEN COALESCE(rtovr.ratingShortDesc,rt.ratingShortDesc) Else null END) as Value_December_12, MAX(Case When r.DateType = 'M' and Month(r.StartDate) = 12 Then dbo.udfGetRating(case when r.RatingOverride is null then r.Rating else r.RatingOverride end) + '' + CAST(UserOverride AS CHAR(1)) + '' + rt.ratingShortDesc Else null END) as Rating_December_12, max(Case When r.DateType = 'Y' and Month(r.StartDate) = 1 AND r.result IS NOT NULL Then r.Result When r.DateType = 'Y' and Month(r.StartDate) = 1 AND r.result IS NULL AND r.rating is NOT NULL THEN COALESCE(rtovr.ratingShortDesc,rt.ratingShortDesc) Else null END) as Value_YTD_13, MAX(Case When r.DateType = 'Y' and Month(r.StartDate) = 1 Then dbo.udfGetRating(case when r.RatingOverride is null then r.Rating else r.RatingOverride end ) + '' + CAST(UserOverride AS CHAR(1)) + '' + rt.ratingShortDesc Else null END ) as Rating_YTD_13 From #tblResults R

Yes, you can build PIVOT dynamically either with the PIVOT statement or CASE expression:
http://www.sqlusa.com/bestpractices2005/dynamicpivot/
First develop the static SQL and test it. Then change it into dynamic.
Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
New Book / Kindle: Exam 70461 Bootcamp: Querying Microsoft SQL Server 2012 
Hi,
Here is a much pared down version of what I think you are trying to accomplish. You can definitely clean up the code and use Pivot to get what you need.
Create Table #Test(EmployeeID int, EmployeeGroupID int, StartDate datetime, Result int, Rating int); Insert #Test(EmployeeID, EmployeeGroupID, StartDate, Result, Rating) Values (1,1,'20130101',25,30), (1,1,'20130101',215,10), (1,1,'20130201',125,230), (1,1,'20130201',2,17), (2,2,'20130301',325,330), (2,2,'20130301',425,430), (2,2,'20130301',525,530); Select * From ( Select v.EmployeeID ,v.EmployeeGroupID ,DATENAME(MONTH,v.StartDate) + ' Result' as Mnth ,v.Result as Value From #test v UNION Select v.EmployeeID ,v.EmployeeGroupID ,DATENAME(MONTH,v.StartDate) + ' Rating' as Mnth ,v.Rating as Value From #test v ) as SourceTable PIVOT ( MAX(Value) FOR Mnth in([January Result],[January Rating],[February Result],[February Rating],[March Result],[March Rating]) ) AS PivotTable; Drop Table #Test;
Martina White
 Edited by Martina WhiteMVP Friday, October 04, 2013 11:47 PM
 Proposed as answer by MajaS Saturday, October 26, 2013 3:08 AM