SQL Server Pivot Table - Dynamic

Question

I have a table, called #MonthlySales, that pulls up ItemNumber, MonYearNum & UnitsSold. I pivoted this with the following script:

SELECT 
    ITEMNUMBER [SKU]
    , ISNULL([1-2011], 0) [Jan 11]
    , ISNULL([2-2011], 0) [Feb 11]
    , ISNULL([3-2011], 0) [Mar 11]
    , ISNULL([4-2011], 0) [Apr 11]
    , ISNULL([5-2011], 0) [May 11]
    , ISNULL([6-2011], 0) [Jun 11]
    , ISNULL([7-2011], 0) [Jul 11]
    , ISNULL([8-2011], 0) [Aug 11]
    , ISNULL([9-2011], 0) [Sep 11]
    , ISNULL([10-2011], 0) [Oct 11]
    , ISNULL([11-2011], 0) [Nov 11]
    , ISNULL([12-2011], 0) [Dec 11] 

, ISNULL([1-2012], 0) [Jan 12]
, ISNULL([2-2012], 0) [Feb 12]
, ISNULL([3-2012], 0) [Mar 12]
, ISNULL([4-2012], 0) [Apr 12]
, ISNULL([5-2012], 0) [May 12]
, ISNULL([6-2012], 0) [Jun 12]
, ISNULL([7-2012], 0) [Jul 12]
, ISNULL([8-2012], 0) [Aug 12]
, ISNULL([9-2012], 0) [Sep 12]
, ISNULL([10-2012], 0) [Oct 12]
, ISNULL([11-2012], 0) [Nov 12]
, ISNULL([12-2012], 0) [Dec 12] 


FROM 
    #MONTHLYSALES
PIVOT 
    (
        SUM(UNITSSOLD) FOR MONTHYEARNUM IN 
                                    (
                                    [1-2011], [2-2011], [3-2011], [4-2011], [5-2011], [6-2011], [7-2011], [8-2011], [9-2011], [10-2011], [11-2011], [12-2011]
                                    ,[1-2012], [2-2012],
 [3-2012], [4-2012], [5-2012], [6-2012], [7-2012], [8-2012], [9-2012], [10-2012], [11-2012], [12-2012]
                                    )
    ) AS TOTSOLDPERMONTH
ORDER BY
    ITEMNUMBER

As you can see, the MonthYearNum column is hardcoded into values like 1-2011. However, I cannot hardcode these, since they are dynamic - depending on which day this report is ran, different MonthYearNum values will be returned from #monthlysales table.

Is there a way for me to dynamically pivot the values that are returned?

TIA

score 1 · Accepted Answer · edited May 23 '17 at 10:25

1

Yep--write dynamic SQL. It's complex, and rather than rewrite similar code ('cause I'm lazy), please refer to my answer to this question for a sample. It's code for a dynamic unpivot, but the concept is the same.

edited May 23 '17 at 10:25

Community

1
1

answered May 03 '12 at 18:51

Philip Kelley

39,426
11
57
92

Hey, thanks. In your example, what would the GetTableColumnNames function look like? – Rivka May 03 '12 at 18:58
That was a function defined by the person posting the question -- ah, the code for it is listed above, in the actual question. – Philip Kelley May 03 '12 at 21:44

score 0 · Answer 2 · answered May 03 '12 at 18:54

0

Alternatively - what is this being output to? SSRS, Report Builder, Excel and most third party reporting tools will do the crosstabulation for you.

answered May 03 '12 at 18:54

ConcernedOfTunbridgeWells

64,444
15
143
197

True. Dynamic pivots in SQL are a royal pain in the keister. – Philip Kelley May 03 '12 at 18:56

SQL Server Pivot Table - Dynamic

2 Answers2