Column headers to column values

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)

Column headers to column values

Author

Topic

Spionred
Starting Member

8 Posts

Posted - 2009-05-27 : 20:10:38

Hi,

I have a view that creates a single row table. What I need however is to take the column headers and place them in a column and the corresponding values in the next column.

E.G.


Item A | Item B | Item C
------------------------
  123     456      789

Into

  Name | Value
----------------
Item A | 123
Item B | 456
Item C | 789

How do you do this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-27 : 21:17:44

[code]
select [Name] = 'Item A',
Value = [Item A]
from yourtable

union all

select [Name] = 'Item B',
Value = [Item B]
from yourtable

union all

select [Name] = 'Item C',
Value = [Item C]
from yourtable
[/code]

KH
[spoiler]Time is always against us[/spoiler]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-28 : 07:17:23

declare @tab table(ItemA int, ItemB int, ItemC int)
insert into @tab select
123 , 456 , 789

select items,val from @tab
unpivot ( val for items in (itema,itemb,itemc))p

a.rameshk
Starting Member

19 Posts

Posted - 2009-05-31 : 07:32:20

By using UNPIVOT also we can get the results
SELECT [NAME],[VALUE]
FROM(SELECT ITEMA,ITEMB,ITEMC
FROM ITEMS)P
UNPIVOT
(VALUE FOR [NAME] IN (ITEMA,ITEMB ,ITEMC)) AS UNPIVOTS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-31 : 14:14:21

quote:
Originally posted by a.rameshk

By using UNPIVOT also we can get the results
SELECT [NAME],[VALUE]
FROM(SELECT ITEMA,ITEMB,ITEMC
FROM ITEMS)P
UNPIVOT
(VALUE FOR [NAME] IN (ITEMA,ITEMB ,ITEMC)) AS UNPIVOTS

As simplied form is what bklr gave already

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources