question on unpivoting the table
hello can anyone tell me the code to unpivot the table
to unpivot which table? I think you will need to be more specific with your question to get an answer that will help you in detail.
Best Answer
It would really help if you provided the dataset that you want to UNPIVOT but here is a simple example for this set of data...
Which is generated with the following CTE SQL...
WITH aDataSet
AS
(
SELECT 12345 [ProductID],
62393.57 [2008SalesTotals],
71141.45 [2009SalesTotals]
UNION ALL
SELECT 67890, 70996.23, 54378.33
UNION ALL
SELECT 23456, 98734.10, 53784.65
UNION ALL
SELECT 98765, 75893.54, 98756.54
UNION ALL
SELECT 98357, 42890.55, 63489.54
)
The UNPIVOT command would then look something like...
SELECT ProductID,
SaleYear,
SaleTotal
FROM
(
SELECT ProductID,
[2008SalesTotals] [2008],
[2009SalesTotals] [2009]
FROM aDataSet
) AS udt
UNPIVOT
(
SaleTotal FOR SaleYear
IN ([2008], [2009])
) AS upvt
This would output a resultset like so...
I hope that helps you out.
The first place to look is BOL - PIVOT and UNPIVOT are explained here : http://msdn.microsoft.com/en-us/library/ms177410.aspx
