UNPIVOT help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

UNPIVOT help

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2008-04-24 : 16:05:44

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
...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-24 : 16:08:53

I haven't read it fully yet, but you should read this:
http://weblogs.sqlteam.com/jeffs/archive/2008/04/23/unpivot.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Hommer
Aged Yak Warrior

808 Posts

Posted - 2008-04-24 : 16:30:52

Thank you for the link.

Let me see if I could work through this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources