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

Answer 1

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

Answer 2

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