SQL Server : how to pivot where new columns have multiple unique values

Question

Using this table :

| CUST | PRODUCT | QTY | SMALLEST |
-----------------------------------
|    E |    1600 |   2 |        1 |
|    F |    1600 |   6 |        9 |
|    G |    1600 |   1 |        8 |

I want to pivot it to make it look like this:

|    E    |    F    |   G    |
------------------------------
|    1600 |    1600 |   1600 |
|    2    |    6    |   1    |
|    1    |    9    |   8    |

I get how to do this with one unique row. If I only have QTY or only have SMALLEST it works fine with this query for example:

Query

SELECT E, F, G
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT (SUM(QTY) FOR CUST IN (E,F,G)) AS pvt

Output

| E | F | G |
-------------
| 2 | 6 | 1 |

But, if I add another column to the concoction in another query, I get this mess:

Query

SELECT E, F, G
FROM (
SELECT CUST, PRODUCT, QTY, SMALLEST
FROM Product) up
PIVOT (SUM(QTY) FOR CUST IN (E,F,G)) AS pvt

Output

|      E |      F |      G |
----------------------------
|      2 | (null) | (null) |
| (null) | (null) |      1 |
| (null) |      6 | (null) |

And it's easy to see why when I change the query:

Query

SELECT product,smallest, E, F, G
FROM (
SELECT CUST, PRODUCT, QTY, SMALLEST
FROM Product) up
PIVOT (SUM(QTY) FOR CUST IN (E,F,G)) AS pvt

Output

| PRODUCT | SMALLEST |      E |      F |      G |
-------------------------------------------------
|    1600 |        1 |      2 | (null) | (null) |
|    1600 |        8 | (null) | (null) |      1 |
|    1600 |        9 | (null) |      6 | (null) |

The pattern becomes obvious. It's finding where there is 1600 and 1 there is one E value: 2, where there is 1600 and 8 there is one F value 6, etc.

I get the problem, but I have no clue how to fix it. Can someone aid me in my futile quest?

SQL Fiddle

Which version of SQL Server are you using? – Hart CO Jul 18 '13 at 16:05 — Hart CO, Jul 18 '13 at 16:05

Kenneth Fisher · Accepted Answer · 2013-07-18T19:54:42.217

Really you want an UNPIVOT to take care of the multiple columns, then a PIVOT to get your data. My favorite way of doing an UNPIVOT is to use CROSS APPLY, but you can do it however you want. Like such.

SELECT E, F, G
FROM (
SELECT CUST, ColumnName, Value
FROM Product
CROSS APPLY ( VALUES ('PRODUCT', PRODUCT),
                        ('QTY', QTY),
                        ('SMALLEST',SMALLEST)) 
    UnPivoted(ColumnName, Value)) up
PIVOT (SUM(VALUE) FOR CUST IN (E,F,G)) AS pvt

And of course if you want to see what column the values are coming from:

SELECT ColumnName, E, F, G
FROM (
SELECT CUST, ColumnName, Value
FROM Product
CROSS APPLY ( VALUES ('PRODUCT', PRODUCT),
                        ('QTY', QTY),
                        ('SMALLEST',SMALLEST)) 
    UnPivoted(ColumnName, Value)) up
PIVOT (SUM(VALUE) FOR CUST IN (E,F,G)) AS pvt

EDIT:

Here is a solution for multiple types. Basically you have to have do multiple queries, one for each data type. Among other things you have a SUM in your numeric query that won't work on varchar columns. Your output also has to be all the same. Meaning you have to convert any numeric or date columns into varchar.

SELECT ColumnName, CAST(E AS varchar(30)) E, CAST(F AS varchar(30)) F, CAST(G AS varchar(30)) G
FROM (
SELECT CUST, ColumnName, Value
FROM Product
CROSS APPLY ( VALUES ('PRODUCT', PRODUCT),
                        ('QTY', QTY),
                        ('SMALLEST',SMALLEST)) 
    UnPivoted(ColumnName, Value)) up
PIVOT (SUM(VALUE) FOR CUST IN (E,F,G)) AS pvt
UNION ALL
SELECT ColumnName, E, F, G
FROM (
SELECT CUST, ColumnName, Value
FROM Product
CROSS APPLY ( VALUES ('CharColA', CharColA),
                        ('CharColB', CharColB)) 
    UnPivoted(ColumnName, Value)) up
PIVOT (MIN(VALUE) FOR CUST IN (E,F,G)) AS pvt

Is this supposed to work with varchar columns? After adding one, I get the error "Error converting data type varchar to float." — Jared Beach, Jul 18 '13 at 18:16
The columns in the CROSS APPLY have to all end up as the same type for it to work this way since they are all ending up in the same column at one point. You could convert everything to varchar though. Or do it in 2 different groups of queries. One for numeric values and another for varchar. Then `UNION ALL` the queries together. — Kenneth Fisher, Jul 18 '13 at 18:58
At what point in the query do i convert to varchar? That would be a good option for me, but I can't just cast(values as char) can I? — Jared Beach, Jul 18 '13 at 19:04
I mean, is there a way to do the conversion in one line and keep it as pretty as it looks now? I know I can convert each column if I have to — Jared Beach, Jul 18 '13 at 19:12

Hart CO · Answer 2 · 2013-07-18T16:14:05.830

1

You can do this with CROSS-APPLY and PIVOT:

SELECT E,F,G
FROM
(
  SELECT Cust,col,value
  FROM #Product
  CROSS APPLY
  (
    VALUES ('E', Product),('F', QTY),('G', SMALLEST)
  ) C (COL, VALUE)
) SRC
PIVOT
(
  MAX(VALUE)
  FOR CUST IN (E,F,G)
) PIV

Demo: SQL Fiddle

edited Jul 18 '13 at 16:14

answered Jul 18 '13 at 15:36

Hart CO

34,064
6
48
63

I like it for the most part, but it seems like your query will just get huge for larger numbers of columns. Thanks for the speedy reply! +1 – Jared Beach Jul 18 '13 at 15:49
Yeah, I'm thinking on a better way, guessing someone else will have one. – Hart CO Jul 18 '13 at 15:58
I think what I need to do is unpivot and then pivot like this threads talks about, but I'm not sure - [thread](http://stackoverflow.com/questions/14694691/sql-server-pivot-table-with-multiple-column-aggregates) – Jared Beach Jul 18 '13 at 16:05
I was just reading an answer like that: http://stackoverflow.com/questions/15297809/sql-transpose-full-table – Hart CO Jul 18 '13 at 16:10
Updated to use `CROSS APPLY` – Hart CO Jul 18 '13 at 16:14
Ah, Kenneth beat me to it, I just used Bluefeet's answer from the thread above and popped in your code. – Hart CO Jul 18 '13 at 16:15
You were really helpful, though. Thanks! – Jared Beach Jul 18 '13 at 16:26

SQL Server : how to pivot where new columns have multiple unique values

2 Answers2