none
How to dynamically create multiple Pivot join

    Question

  • 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  '2012-01-01' AND '2012-05-01'
                   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  '2012-01-01' AND '2012-05-01'
                   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  '2012-01-01' AND '2012-05-01'
                   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  '2012-01-01' AND '2012-05-01'
                   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

    Tuesday, September 24, 2013 9:45 PM

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

    Wednesday, September 25, 2013 3:09 AM
  • 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

    Wednesday, September 25, 2013 4:08 PM
  • 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 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Thursday, October 03, 2013 7:41 PM
  • 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,'2013-01-01',25,30),
    (1,1,'2013-01-01',215,10),
    (1,1,'2013-02-01',125,230),
    (1,1,'2013-02-01',2,17),
    (2,2,'2013-03-01',325,330),
    (2,2,'2013-03-01',425,430),
    (2,2,'2013-03-01',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 White Friday, October 04, 2013 11:47 PM
    • Proposed as answer by MajaS Saturday, October 26, 2013 3:08 AM
    Friday, October 04, 2013 11:46 PM