T-SQL help with multiple unpivot

Question

Hello

I was hoping someone could help me with this

I have data like this :

SERVICE ITEM1    ITEM2    ITEM3    ITEM4  
GOLD    0        0        0        0
SILVER  2      2       0        2  
BRONZE  0      4       0        26  

But want it like this:

GOLD  SILVER    BRONZE
2  0        0
2  0        4
0  0        0
2  0        26

I think this is an unpivot, but cant figure out the syntax

many thanks

Answer 1

Get familiar with PIVOT

Answer 2

/*Create testing Data - You don't need this*/
SELECT * 
INTO #YourTable FROM 
(
SELECT 'GOLD' AS SERVICE, 0 AS ITEM1, 0 AS ITEM2, 0 AS ITEM3, 0 AS ITEM4 UNION ALL 
SELECT 'SILVER' AS SERVICE, 2 AS ITEM1, 2 AS ITEM2, 0 AS ITEM3, 2 AS ITEM4 UNION ALL 
SELECT 'BRONZE' AS SERVICE, 0 AS ITEM1, 4 AS ITEM2, 0 AS ITEM3, 26 AS ITEM4
) testdata


/*You just need this*/    
SELECT * 
FROM 
    /*Substitute the name of your table here*/
    (SELECT SERVICE, ITEM1, ITEM2, ITEM3, ITEM4 FROM #YourTable) BaseData
UNPIVOT
   (Vals FOR Item IN 
      (ITEM1, ITEM2, ITEM3, ITEM4)
 )AS unpvt
 PIVOT (SUM(Vals) FOR SERVICE IN ([GOLD],[SILVER],[BRONZE]) ) AS pvt;