Comma Separated String from Dynamic Table

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 2008 Forums

Transact-SQL (2008)

Comma Separated String from Dynamic Table

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2012-04-30 : 03:07:42

Hi,
Need help to get CSV from Dynamic Data table. Were as Row name and Column name need to be concatenated with comma separated values.

Ex: (My Dynamic Data Table - TableA)

[ROW] [COL1] [COL2] [COL3] [COL4] [COL5] [COL6] .... [COLn]
1 Y Y N N N N Y
2 N Y Y Y N Y N
3 N N N N Y Y Y

Here the Data table contains 'n' no of columns and Row Column.
Have to take 'Y' values in CSV with concatenating ROW value and COLUMN names.
Ex: 1st ROW values--> 1-COL1,1-COL2,1-COLn

Output: (In single String)
-------
1-COL1,1-COL2,1-COLn,2-COL2,2-COL3,2-COL4,2-COL6,3-COL5,3-COL6,3-COLn

Please help me to built a Query for this.

Regards,
Kalai

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-30 : 04:58:07

[code]

declare @colname nvarchar(100),
@output nvarchar(max),
@sql nvarchar(max)

select @colname = min([name])
from sys.columns
where object_id = object_id('tbl')
and [name] like 'col%'

select @sql = N'with cte as ('

while @colname is not null
begin
select @sql = @sql
+ N'select row, col = ''' + @colname + ''''
+ N'from tbl '
+ N'where ' + @colname + ' = ''Y'' '

select @colname = min([name])
from sys.columns
where object_id = object_id('tbl')
and [name] like 'col%'
and [name] > @colname

if @colname is not null
begin
select @sql = @sql
+ N'union all '
end
end

select @sql = @sql
+ N')'
+ N'select @output = isnull(@output + '','', '''') '
+ N'+ convert(varchar(10), row) + ''-'' + col '
+ N'from cte '
+ N'order by row, col'

-- print @sql
exec sp_executesql @sql, N'@output varchar(max) OUTPUT', @output OUTPUT

print @output
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources