unpivot row values into multiple columns

Question

I have the following with the following structure:

CREATE TABLE Prices
(
Day date,
ProductName nVARCHAR(10),
Location nVARCHAR(10),
RegPrice1 float,
SalePrice1 float,
SalePrice2 float
)

INSERT INTO Prices VALUES ('6/24/2014', 'xbox', 'NewYork',30,20,10)
INSERT INTO Prices VALUES ('6/24/2014', 'xbox', 'London', 100,80,60)
INSERT INTO Prices VALUES ('6/24/2014', 'xbox', 'Singapore', 70,50,30)
INSERT INTO Prices VALUES ('6/24/2014', 'watch1','NewYork', 500,400,300)
INSERT INTO Prices VALUES ('6/24/2014', 'watch1','London', 1000,800,600) 
INSERT INTO Prices VALUES ('6/24/2014', 'watch1','Singapore', 999,888,777)

I want to unpivot this table so it looks like:

Day           Pr_Name PriceType   NewYork London  Singapore
2014-06-24    xbox    RegPrice1   30      100     70
2014-06-24    xbox    SalePrice1  20      80      50
2014-06-24    xbox    SalePrice2  10      60      30
2014-06-24    watch1  RegPrice1   500     1000    999
2014-06-24    watch1  SalePrice1  400     800     888
2014-06-24    watch1  SalePrice1  300     600     777

I was able to unpivot one layer to get the NewYork column but I haven't been able to get the London and Singapore columns in place. I have tinkered with the code below to add London and Singapore but have not been successful. Do I simply keep unpivoting?

select Day, ProductName, PriceType, NewYork
from (select * from Prices ) as t
Unpivot 
(
  NewYork for PriceType in (RegPrice1, SalePrice1, SalePrice2)

) As unpvt

radar · Accepted Answer · 2014-09-18T18:54:07.753

2

we can use CROSS APPLY to unpivot the prices and then apply PIVOT

SELECT 
*
FROM

  ( select Day, ProductName, Location, col,  value
    from Prices
    cross apply
    (
        select 'RegPrice1' , Prices.RegPrice1 union all
        select 'SalePrice1', Prices.SalePrice1 union all
        select 'SalePrice2', Prices.SalePrice2
    ) c (col, value)
  ) PD
  PIVOT
  ( max(value) for Location in ([NewYork],[London],[Singapore])
  )pvt

edited Sep 18 '14 at 18:54

answered Sep 18 '14 at 18:18

radar

13,270
2
25
33

Thanks for the quick reply. I don't think this is quite what I'm looking for. I want to stack all of the price types in one column. Please see result table above (I updated it with the full result table). – codingknob Sep 18 '14 at 18:30
updated it , but still think there is better way where we can first unpivot and then need to pivot – radar Sep 18 '14 at 18:40

unpivot row values into multiple columns

1 Answers1