Column on splitted values - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Column on splitted values

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-06-17 : 07:44:22

I have a split function tat splits comma seperated values.After splitting the values are say
1
2
3
Would like to change the rows to columns dynamically


col1 col2 col3
1     2    3

If it is
1
2
output should be


col1 col2 
1     2

Thanks for any help

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-17 : 07:52:04

see this link it may useful
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-06-17 : 08:06:26

Hi, Try this once,



declare @temp table ( id int)
insert into @Temp select 1
insert into @Temp select 2
insert into @Temp select 3

select [1] as col1,[2] as col2,[3] as col3 from
(select * from @temp ) t
pivot ( max(id) for id in ( [1],[2],[3])) t1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-17 : 10:26:37

quote:

Originally posted by Nageswar9

Hi, Try this once,



declare @temp table ( id int)
insert into @Temp select 1
insert into @Temp select 2
insert into @Temp select 3

select [1] as col1,[2] as col2,[3] as col3 from
(select * from @temp ) t
pivot ( max(id) for id in ( [1],[2],[3])) t1

you need to do this dynamically as you're not sure of values in column id. see link posted by bklr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources