Horizontal to vertical - 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

Horizontal to vertical

Author

Topic

GhantaBro
Posting Yak Master

215 Posts

Posted - 2010-07-09 : 16:57:46

I have done vertical to horizontal, but this time I need horizontal data to vertical... I have created one script, but they need without cursor at work...checking if someone has already done this..thanks a lot

col1 col2 col3 col4
aa 1234 tyf bbb
kc 3453 bnd vdk

output should be:

aa
1234
tyf
bbb
kc
3453
bnd
vdk

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-07-09 : 17:23:01

Look in SQL Server Books Online for "UNPIVOT".

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-10 : 02:35:08

another solution :)


create table p
(name char(10)
,n int)

insert into p
select 'AA',10 union all
select 'AA',12 union all
select 'AA',4 union all
select 'BB',11 union all
select 'CC',51 union all
select 'CC',33


select 
	cast(name as varchar(20)) as r
   ,row_number() over(order by name asc) as RN
   ,1 as table_order
into temp
From p
union all
select
	cast(n as varchar(20))  as r
   ,row_number() over(order by name asc) as RN
   ,2 as table_order
from p
order by RN, table_order


select r from temp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources