Concatenated values as columns

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)

Concatenated values as columns

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-06 : 02:33:08

Hi guys,
[url][/url]
I want to display the resultset depending upon the number of the concatenated
values in the row.But those values can be dynamic
There can be min 2 concatenated values per row & n number of concatened values per row

declare @table table(id int identity(1,1),[Values] varchar(200))
insert into @table
select '1,2,3,4' union all
select '1,2' union all
select '8,9'

Note in the above example the number of columns should be 4 because there are max 4 values in the row.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-06 : 03:46:56

One of the methods
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/csv-to-multiple-columns.aspx

Madhivanan

Failing to plan is Planning to fail

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-06 : 04:39:19

No Madhivinan.You solution will work only if your values are of fixed length.
As I had said that the length of rows can be dynamic.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-06 : 07:49:28

See if point 8 would help you
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-06 : 09:11:15

No madhi that one also did not help.
But I did make some progress.SomeHow I could get the the number of columns I need to have.I mean till now I could get the resultset like this

Count Data
4 1,2,3,4
2 1,2
2 8,9

Now I know that the maximum columns need to be 4.But how can I go further from here.
Please help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-06 : 09:37:27

Try

declare @table table(id int identity(1,1),[Values] varchar(200))
insert into @table
select '1,2,3,4' union all
select '1,2' union all
select '8,9' 

declare @i int
select @i=max(len([values])-len(replace([values],',',''))) from @table
print @i

update @table
set [values]=[values]+replicate(',',@i-1) from @table
where len([values])-len(replace([values],',',''))<>@i

declare @s varchar(8000), @data varchar(8000)

select @s=''

while exists (Select * from @table where [values]>@s)
Begin
        Select @s=min([values]) from @table where [values]>@s
        select @data=''''+replace(@s,',',''',''')+''''
        exec('select '+@data)
End

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources