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?

Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)

stevenmahony
– stevenmahony

2013-06-04 12:12:03 +00:00
Commented Jun 4, 2013 at 12:12 — stevenmahony
– stevenmahony, Commented Jun 4, 2013 at 12:12

Taryn · Accepted Answer · 2013-06-04 12:15:10Z

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 |

On a side not, I fixed the table structure, so there was no need for the cross-apply but good to know when the table structure cant be modified. Thanks again.

Collectives™ on Stack Overflow

SQL - Pivot multiple columns without Aggregrates

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related