July 9, 2010 at 3:03 pm
I have done vertical to horizontal, but this time I need horizontal data to vertical... 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
July 9, 2010 at 4:48 pm
Two options come to mind:
1. Use the UNPIVOT command (http://msdn.microsoft.com/en-us/library/ms177410.aspx)
2. Assuming four columns:
SELECT CASE NUM
WHEN 1 THEN Col1
WHEN 2 THEN Col2
WHEN 3 THEN Col3
WHEN 4 THEN Col4
END
FROM
(
SELECT col1, col2, col3, col4, num
FROM myTable
CROSS JOIN (select 1 as num union all select 2 as num union all select 3 as num union all select 4 as num ) N
) h
I haven't parsed the code, but it should give you the general idea.
SQL guy and Houston Magician
July 10, 2010 at 12:27 am
Im hope I have understood the question right,
I think all you need is a function that would accept a string list and out put the data in table format right ?
July 10, 2010 at 7:43 am
Try this:
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
July 10, 2010 at 7:58 am
All you need is to do unpivot.. here is how u do that.. this produces 2 column, with the column name as the second column.. remove "star" from the query and replace with ColVals u ll get ur output..
if object_id('tempdb..#temp') is not null
drop table #temp
create table #temp
(
col1 char(10) ,
col2 char(10) ,
col3 char(10) ,
col4 char(10)
)
insert into #temp (col1 ,col2 ,col3 ,col4)
select 'aa', '1234', 'tyf','bbb' union all
select 'kc', '3453', 'bnd','vdk'
select * from
(select col1 ,col2 ,col3 ,col4 from #temp) pivot_table
unpivot
( ColVal for Rows in ([col1] , [col2] , [col3] , [col4])) pivot_handle
HTH
~Edit : changed from pivot to unpivot
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply