How to put data in 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)

How to put data in columns

Author

Topic

vmon
Yak Posting Veteran

63 Posts

Posted - 2006-09-05 : 11:29:46

I have a table with three columns: Item, Property, Value
I want to essentially cross-tab the Property and show the values each item has for each of the Property's. Should I be trying a cross tab? Union?
Everything I have tried so far is stumping me. At this point the Property is only 3 different possibilities but will be variable in future. The cross-tab is requiring calcualtions and all I want is the data which in most cases is not numeric. The union is not wokring becasue of the need for different columns.

thanks for your help,
vmon

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-05 : 11:34:18

Post some sample data, the query you tried and expected output for us to get better idea of your problem.

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 11:41:25

Try this

SELECT		Item,
		MAX(CASE WHEN Property = 'Option1' THEN Value END) Option1,
		MAX(CASE WHEN Property = 'Option2' THEN Value END) Option2,
		MAX(CASE WHEN Property = 'Option3' THEN Value END) Option3
FROM		YourTable
GROUP BY	Item

Peter Larsson
Helsingborg, Sweden

vmon
Yak Posting Veteran

63 Posts

Posted - 2006-09-05 : 11:42:30

Here is some data. I want the result columns to be ItemId, Thickness, Width, Length showing the values which could also be text.

ItemId PropertyId ValueId
00802680404G Thickness 1/4
00802680404G Width 8-3/8
00802680404G Length 12-5/8
1003V102 Thickness 5/8
1003V102 Width 2-15/16
1003V102 Length 102
1003V108 Thickness 5/8
1003V108 Width 2-15/16
1003V108 Length 108
1003V114 Thickness 5/8
1003V114 Width 2-15/16
1003V114 Length 114

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-05 : 12:11:03

[code]Select ItemID,
case PropertyID when 'Thickness' then ValueId Else '' End as Thickness,
case PropertyID when 'Width' then ValueId Else '' End as Width,
case PropertyID when 'Length' then ValueId Else '' End as Length,
from Table[/code]

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources