Transpose rows to column - 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

Transpose rows to column

Author

Topic

shaggy
Posting Yak Master

248 Posts

Posted - 2009-06-09 : 09:56:54

I want to Transpose rows to column this is what i have column name and one row

id name
1 test

i want result like this
id 1
name test

Plz help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-09 : 12:01:29

[code]
select val1,val2
from
(
select 'id' as val1,cast(id as varchar(100)) as val2
from yourtable
union all
select 'name' ,name
from yourtable
)t
[/code]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-10 : 01:14:35

[code]
try this by unpivot also
declare @test table( id int, name varchar(32))
insert into @test select 1,'test'

select val1,cast(val2 as varchar(100)) AS val2
from @test
unpivot (val2 for val1 in (id)) p
union all
select val1,val2
from @test
unpivot (val2 for val1 in (name)) p
[/code]

shaggy
Posting Yak Master

248 Posts

Posted - 2009-06-10 : 03:09:48

Thanks visakh16 & bklr
If there is multiple column
id name
1 test
2 test1

i want result like this
id 1
name test

shaggy
Posting Yak Master

248 Posts

Posted - 2009-06-10 : 03:10:47

Thanks visakh16 & bklr
If there is multiple rows
id name
1 test
2 test1

i want result like this
id 1 2
name test test1

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-10 : 08:46:18

try this shaggy,


declare @test table( id int, name varchar(32))
insert into @test select 1,'test'
insert into @test select 2,'test1'
insert into @test select 3,'test4'

select val1,[1],[2],[3]
from (select row_number()over(partition by val1 order by val1) as rid,val1,cast(val2 as varchar(100)) AS val2
from @test
unpivot (val2 for val1 in (id)) p
union all 
select row_number()over(partition by val1 order by val1) as rid,val1,val2
from @test
unpivot (val2 for val1 in (name)) p)s
pivot(max(val2) for rid in ([1],[2],[3]))p

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-10 : 13:44:02

see this

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources