UNPIVOT Help

Hi, all,

Could some body give me little help on this? The UNPIVOT is new to sql05, and I did not find many useful helps out there.

My understanding is that it will normalize the data in column name (the UNPIVOT FOR part). As my sample shows here, I want to get that from one of the row.

Here is the sample, and of cause the Select PIVOT part is not working.

Also, if UNPIVOT is not the right way to go after it, what is the right way?

Thanks!


USE tempdb;
GO
If Object_ID('#pvtSales') IS NOT NULL
Drop table dbo.#pvtSales;
GO

Create Table #pvtSales (Prod_ID varchar(10), Prod_desc varchar(10), Col3 varchar(12), Col4 varchar(10), Col5 varchar(12), Col6 varchar(10), Col7 varchar(12), Col8 varchar(10))

INSERT INTO #pvtSales
Select '111', 'my goody', '1', '1.5', '0', '1.5', '1', '1.3' UNION ALL
Select '222', 'my stuff', '0', '0.5', '1', '0.5', '1', '1.0' UNION ALL
Select NULL, NUll, '2007-06-03', NULL, '2007-06-10', NULL, '2007-06-17', NULL UNION ALL
Select '333', 'my goody3', '1', '2', '0', '2', '1', '2'

Select Prod_ID, Prod_desc, Col3, Col4
FROM #pvtSales
UNPIVOT (Col4 for Col3 in (['06/03/2007'],['06/10/2007'],['06/17/2007']) AS U


The result I am looking for:

Prod_ID/Prod_desc/Sale/Cost/SaleDate
111/my goody/1/1.5/2007-06-03
111/my goody/0/1.5/2007-06-10
111/my goody/1/1.3/2007-06-17
222/my stuff/0/0.5/2007-06-03
...