Convert column into row - 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

Convert column into row

Author

Topic

alanhuro
Starting Member

34 Posts

Posted - 2009-03-30 : 15:19:08

Hi guys,

How are you doing. I have a hard time in the past converting row to column and now I have a hard time converting row into column. This is an example



Var1   Var2    Var3    Var3 Description  date
123    233     234     243  Test A       2009-03-01 00:00:00
1      23       34      43  Test B       2009-03-01 01:00:00
12     33      234      24  Test C       2009-03-01 02:00:00

convert To

Var    Description  Date
123    Test A       2009-03-01 00:00:00
233    Test A       2009-03-01 00:00:00
234    Test A       2009-03-01 00:00:00
243    Test A       2009-03-01 00:00:00
1      Test B       2009-03-01 01:00:00
23     Test B       2009-03-01 01:00:00
34     Test B       2009-03-01 01:00:00
43     Test B       2009-03-01 01:00:00

without using union?

Thanks
Alan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-30 : 15:22:59

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

"Let's begin with the premise that everything you've done up until this point is wrong."

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-30 : 23:03:54

[code]
if u r using 2005 version and above
it will work
declare @tab table(Var1 int,Var2 int,Var3 int, Var4 int, Description varchar(32), date datetime)
insert into @tab select
123, 233, 234, 243, 'Test A', '2009-03-01 00:00:00'
insert into @tab select
1 , 23, 34, 43, 'Test B', '2009-03-01 01:00:00'
insert into @tab select
12, 33, 234, 24, 'Test C', '2009-03-01 02:00:00'

SELECT val,Description,namevalue, date
FROM @tab
UNPIVOT(val FOR namevalue IN (Var1,Var2,Var3,var4))AS pvt
[/code]

alanhuro
Starting Member

34 Posts

Posted - 2009-03-31 : 14:43:13

Thanks guy, unfortunately my company is still using SQL 2000

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-31 : 14:54:04

Why can't you use UNION?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-01 : 00:51:53

try this one as robvolk suggested,


select var1,description,date from @tab
union 
select var2,description,date from @tab
union 
select var3,description,date from @tab
union 
select var4,description,date from @tab

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources