SQL - Pivot multiple columns without Aggregrates

Question

I am unsure how to pivot my data to the a specific view. Below is the test data.

SQL

CREATE TABLE #tmpData (ProductTitle VARCHAR(100), abvrMonthName VARCHAR(3),abvrMonthNameCount VARCHAR(4),MonthAvg NUMERIC(6,2),MonthCount INT)
INSERT INTO #tmpData SELECT 'Product 1','Dec','Dec#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 1','Nov','Nov#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 1','Oct','Oct#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 1','Sep','Sep#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 1','Aug','Aug#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 1','Jul','Jul#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 1','Jun','Jun#',   5   ,   1
INSERT INTO #tmpData SELECT 'Product 1','May','May#',   4.44    ,   9
INSERT INTO #tmpData SELECT 'Product 1','Apr','Apr#',   5   ,   6
INSERT INTO #tmpData SELECT 'Product 1','Mar','Mar#',   5   ,   4
INSERT INTO #tmpData SELECT 'Product 1','Feb','Feb#',   5   ,   1
INSERT INTO #tmpData SELECT 'Product 1','Jan','Jan#',   5   ,   2
INSERT INTO #tmpData SELECT 'Product 2','Dec','Dec#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 2','Nov','Nov#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 2','Oct','Oct#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 2','Sep','Sep#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 2','Aug','Aug#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 2','Jul','Jul#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 2','Jun','Jun#',   5   ,   1
INSERT INTO #tmpData SELECT 'Product 2','May','May#',   4.67    ,   3
INSERT INTO #tmpData SELECT 'Product 2','Apr','Apr#',   4.33    ,   3
INSERT INTO #tmpData SELECT 'Product 2','Mar','Mar#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 2','Feb','Feb#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 2','Jan','Jan#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 3','Dec','Dec#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 3','Nov','Nov#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 3','Oct','Oct#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 3','Sep','Sep#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 3','Aug','Aug#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 3','Jul','Jul#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 3','Jun','Jun#',   5   ,   3
INSERT INTO #tmpData SELECT 'Product 3','May','May#',   5   ,   6
INSERT INTO #tmpData SELECT 'Product 3','Apr','Apr#',   4   ,   6
INSERT INTO #tmpData SELECT 'Product 3','Mar','Mar#',   4.75    ,   8
INSERT INTO #tmpData SELECT 'Product 3','Feb','Feb#',   4.75    ,   8
INSERT INTO #tmpData SELECT 'Product 3','Jan','Jan#',   4.6 ,   5

SELECT  ProductTitle,[jan],[jan#],[feb],[feb#]
    FROM    
    (   SELECT  *
        FROM    #tmpData        
    ) AS s
    PIVOT
    (
        SUM(MonthAvg) FOR abvrMonthName IN (
            jan,feb,mar,apr,may,jun,jul,aug, sep, oct, nov, [dec]
        )
    ) as p  
    PIVOT
    (
        SUM(MonthCount) FOR abvrMonthNameCount IN (
            jan#,feb#,mar#,apr#,may#,jun#,jul#,aug#, sep#, oct#, nov#, [dec#]
        )
    ) as p1 
    --GROUP BY ProductTitle,[jan],[feb]

DROP TABLE #tmpData

As you can see from the output the ProductTitle is not grouped.

How would I achieve this or am I going in the wrong direction completely?

Taryn Taryn · Accepted Answer · 2013-06-04T12:15:10

Part of the problem is that you have de-normalized data across multiple columns that you want to pivot. Ideally, you should consider fixing your table structure so you it will be easier to maintain and query. If you are not able to fix the table structure, then you should unpivot the columns first to then apply PIVOT to get the final result.

The UNPIVOT process will take the multiple columns and convert them into multiple rows. Depending on your version of SQL Server there are a few ways that you can do this. You can use the UNPIVOT function or since you are using SQL Server 2008, you can use CROSS APPLY with the VALUES clause to unpivot.

The CROSS APPLY/VALUES code will be:

select t.producttitle, c.col, c.value
from tmpData t
cross apply
(
  values (abvrMonthName, MonthAvg), (abvrMonthNameCount, MonthCount)
) c (col, value)

See SQL Fiddle with Demo. This takes your multiple columns and places the data into a format similar to this:

| PRODUCTTITLE |  COL | VALUE |
-------------------------------
|    Product 1 |  Dec |     0 |
|    Product 1 | Dec# |     0 |
|    Product 1 |  Nov |     0 |
|    Product 1 | Nov# |     0 |
|    Product 1 |  Oct |     0 |
|    Product 1 | Oct# |     0 |
|    Product 1 |  Sep |     0 |
|    Product 1 | Sep# |     0 |

Once the data is in this format you can apply the PIVOT to the values in col which contains the month names:

select producttitle, jan, [jan#], feb, [feb#], mar, [mar#], apr, [apr#],
  may, [may#], jun, [jun#], jul, [jul#], aug, [aug#],
  sep, [sep#], oct, [oct#], nov, [nov#], dec, [dec#]
from
(
  select t.producttitle, c.col, c.value
  from tmpData t
  cross apply
  (
    values (abvrMonthName, MonthAvg), (abvrMonthNameCount, MonthCount)
  ) c (col, value)
) d
pivot
(
  sum(value)
  for col in (jan, [jan#], feb, [feb#], mar, [mar#], apr, [apr#],
              may, [may#], jun, [jun#], jul, [jul#], aug, [aug#],
              sep, [sep#], oct, [oct#], nov, [nov#], dec, [dec#])
) piv;

See SQL Fiddle with Demo. This gives a result:

| PRODUCTTITLE | JAN | JAN# |  FEB | FEB# |  MAR | MAR# |  APR | APR# |  MAY | MAY# | JUN | JUN# | JUL | JUL# | AUG | AUG# | SEP | SEP# | OCT | OCT# | NOV | NOV# | DEC | DEC# |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|    Product 1 |   5 |    2 |    5 |    1 |    5 |    4 |    5 |    6 | 4.44 |    9 |   5 |    1 |   0 |    0 |   0 |    0 |   0 |    0 |   0 |    0 |   0 |    0 |   0 |    0 |
|    Product 2 |   0 |    0 |    0 |    0 |    0 |    0 | 4.33 |    3 | 4.67 |    3 |   5 |    1 |   0 |    0 |   0 |    0 |   0 |    0 |   0 |    0 |   0 |    0 |   0 |    0 |
|    Product 3 | 4.6 |    5 | 4.75 |    8 | 4.75 |    8 |    4 |    6 |    5 |    6 |   5 |    3 |   0 |    0 |   0 |    0 |   0 |    0 |   0 |    0 |   0 |    0 |   0 |    0 |

SQL - Pivot multiple columns without Aggregrates

1 Answers