Showing columns as rows - 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

General SQL Server Forums

New to SQL Server Programming

Showing columns as rows

Author

Topic

dprichard
Yak Posting Veteran

94 Posts

Posted - 2009-01-12 : 15:04:31

I have a table with the following columns:

ProductID - Sport - Color - Size

an example of the data in this would be

1001 - Soccer - Red - Large
1002 - Basketball - Blue - Small

I am trying to show the results like this in a separate view

ProductID - Class - Option
1001 - Sport - Soccer
1001 - Color - Red
1001 - Size - Large
1002 - Sport - Basketball
1002 - Color - Blue
1002 - Size - Small

Any help would be greatly appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-12 : 16:43:13

What version of SQL Server are you using?

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

Subscribe to my blog

dprichard
Yak Posting Veteran

94 Posts

Posted - 2009-01-12 : 16:53:22

Sorry, SQL 2005

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-12 : 17:00:51

Check out the UNPIVOT command in SQL Server Books Online. Read up on PIVOT also.

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

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 23:47:20

[code]SELECT *
FROM
(SELECT * FROM Table)t
UNPIVOT(Val FOR Category IN ([Sport],[Color],[Size]))u[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-12 : 23:51:54

SELECT ProductID, UNP.Class, UNP.[Option] FROM
(SELECT CONVERT(sql_variant,ProductID)as ProductID,CONVERT(sql_variant,Sport)as Sport,
CONVERT(sql_variant,Color)as Color,CONVERT(sql_variant,[Size])as [Size]
FROM Table)d
UNPIVOT ([Option] For Class In (Sport, Color, [Size])) as UNP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 23:52:20

[code]declare @Table table
(
ProductID int,
Sport varchar(20),
Color varchar(20),
[Size] varchar(20)
)

insert into @table
select 1001,'Soccer','Red','Large' union all
select 1002 ,'Basketball','Blue','Small'

select ProductID,category,val
from
(select * from @table) t
unpivot(val for category in ([Sport],[Color],[Size]))u

output
---------------------------------------
ProductID category val
1001 Sport Soccer
1001 Color Red
1001 Size Large
1002 Sport Basketball
1002 Color Blue
1002 Size Small[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources