SELECTing Rows AS Columns !? - 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 2000 Forums

Transact-SQL (2000)

SELECTing Rows AS Columns !?

Author

Topic

bluemetal
Starting Member

26 Posts

Posted - 2005-05-03 : 20:18:05

Ok heres the situation, the data looks like this:

NUM VER TYPE VALUE DATE
045 1 BUILD 2.45 24/05/2004
045 2 TOWER 3.55 24/05/2004
045 3 BUILD 4.00 24/05/2004

Can i have these SELECTed in a manner so that they appear like this?

NUM VER BUILD TOWER DATE
045 1 2.45 24/05/2004
045 2 3.55 24/05/2004
045 3 4.00 24/05/2004

Thanks!

bluemetal
Starting Member

26 Posts

Posted - 2005-05-03 : 20:27:16

Shit! The formatting got removed and spoilt the data result, the data result should look something like this.

NUM VER BUILD TOWER DATE
045--1----2.45---------24/05/2004
045--2----------3.55---24/05/2004
045--3----4.00---------24/05/2004

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-05-03 : 20:37:08

You can try this new link that Jeff posted in his blog:

http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

Or the original article he mentions in it:

http://www.sqlteam.com/item.asp?ItemID=2955

I like his version better.

If you have a fixed number of pivot columns (BUILD, TOWER, maybe a few others) then you can just hard-code the CASE expression that does the pivoting. It will be more efficient than using the dynamic SQL techniques that the articles use.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources