UNPIVOT/PIVOT to combine multiple records into one

Question

Currently, I have a table that tracks product inventory locations using the following table:

ProductID(PK)  Location(PK) BIN1    BIN2    
  1000             EAST     XRZY    CCAB
  1000             WEST     AAAA    NULL

I'm attempting to UNPIVOT the data into the following:

ProductID   EAST_BIN1   EAST_BIN2   WEST_BIN1    WEST_BIN2
  1000        XRZY        CCAB         AAAA         NULL

Note that the location column has been PIVOTed into part of the BIN value field.

However, I've found that if I pivot the data, I'm unable to combine it with the BIN fields. PIVOT simply aggregates (using MAX) the BIN values into one field, while UNPIVOT just transforms the BIN* fields into rows.

What am I missing in terms of transforming the data above?

Any help would be greatly appreciated!

@Mihai I'm not sure that I follow, could you provide more detail? — TelJanini, Aug 20 '14 at 20:00
Here is an example of pivot http://stackoverflow.com/questions/5846007/sql-query-to-pivot-a-column-using-case-when — Mihai, Aug 20 '14 at 20:01

score 2 · Answer 1 · answered Aug 20 '14 at 20:04

You can do it "by hand" as follows:

SELECT ProductID,
       MAX(CASE WHEN Location='EAST' THEN BIN1 ELSE NULL END) AS EAST_BIN1,
       MAX(CASE WHEN Location='EAST' THEN BIN2 ELSE NULL END) AS EAST_BIN2,
       MAX(CASE WHEN Location='WEST' THEN BIN1 ELSE NULL END) AS WEST_BIN1,
       MAX(CASE WHEN Location='WEST' THEN BIN2 ELSE NULL END) AS WEST_BIN2
FROM YOURTABLE
GROUP BY ProductID

This creates multiple rows (as your source table) with the results in the correct column, then smashes them down to one row with a group by. The correct value is taken using the aggregate function MAX.

score 0 · Answer 2 · answered Aug 20 '14 at 20:16

To pivot you'll need to get the data into a single BIN column to pivot on. Consider this...

declare @t table (ProductId int, Location varchar(20), BIN1 varchar(4), BIN2 varchar(4));
insert into @t values(1000, 'EAST', 'XRZY', 'CCAB'), (1000, 'WEST', 'AAAA', null);

with cte as (
    select ProductId, Col = Location + '_BIN1', BINVal = Bin1 from @t
    union all
    select ProductId, Col = Location + '_BIN2', BINVal = Bin2 from @t
)

select
    *
from
    cte
    pivot (
        max(BINVal)
        for Col in ([EAST_BIN1], [EAST_BIN2], [WEST_BIN1], [WEST_BIN2])
    ) p

UNPIVOT/PIVOT to combine multiple records into one

2 Answers2