how to convert column values into comma seperated row vlaues

Question

I have a table with values as

FKTABLE_NAME               FKCOLUMN_NAME               PKCOLUMN_NAME
table1                     column1                        column1
table1                     column2                        column2
table2                     column1                        column1
table2                     column2                        column2

How I need to convert this into

FKTABLE_NAME               FKCOLUMN_NAME               PKCOLUMN_NAME

tablel1                    column1,column2                column1,column2
table12                    column1,column2                column1,column2

Basically, I am trying to get the comma seperated columns group by the table name.

thanks

RichardTheKiwi · Accepted Answer · 2011-02-23T11:33:47.403

Here's a working query on any db

select distinct table_name,
  stuff((select ','+data_type
   from information_schema.columns b
   where b.table_name=a.table_name
   for xml path(''),type).value('.[1]','nvarchar(max)'),1,1,'') AS data_types,
  stuff((select ','+column_name
   from information_schema.columns b
   where b.table_name=a.table_name
   for xml path(''),type).value('.[1]','nvarchar(max)'),1,1,'') AS column_names
from information_schema.columns a

And here is your query

select distinct FKTABLE_NAME,
  stuff((select ','+FKCOLUMN_NAME
   from tbl b
   where b.FKTABLE_NAME=a.FKTABLE_NAME
   for xml path(''),type).value('.[1]','nvarchar(max)'),1,1,'') AS FKCOLUMN_NAMES,
  stuff((select ','+PKCOLUMN_NAME
   from tbl b
   where b.FKTABLE_NAME=a.FKTABLE_NAME
   for xml path(''),type).value('.[1]','nvarchar(max)'),1,1,'') AS PKCOLUMN_NAMES
from tbl a

score 0 · Answer 2 · edited May 17 '12 at 22:38

Below SQL statement is used for converting single column data in a single row:

declare @testdata varchar(8000)
set @testdata=''

select @testdata=@testdata+NAME+', ' from User where ID in('1','2','3','4','5')
print @testdata

select substring(@testdata,0,len(@testdata))

In above the above example:

User is a table
NAME is column of user which data will be manuplate in row
ID is column of user

For reference see below link:

http://www.thedevheaven.com/2012/02/column-data-in-to-single-row-with-comma.html

score 0 · Answer 3 · answered Feb 23 '11 at 11:13

0

SELECT [Col1Name] + ',' + [Col2Name] + ',' ... + [ColNName]
FROM [tableName]

answered Feb 23 '11 at 11:13

HuBeZa

4,609
3
34
55

I need to convert column values into comma separated values into a single value group by table name – prav Feb 23 '11 at 11:16
oh, sorry. You want to [swap rows and columns](http://stackoverflow.com/questions/584232/t-sql-how-to-swap-rows-and-columns)? – HuBeZa Feb 23 '11 at 11:19

score 0 · Answer 4 · answered Feb 23 '11 at 16:59

As with all string aggregation problems I advise writing a custom aggregate (and I STRONGLY advise using a clr aggregate for best performance) and write a query like this

select fktablename, stringAggregate(fkcolumnname), stringAggregate(pkcolumnname)
group by 
fktablename

some tips here

how to convert column values into comma seperated row vlaues

4 Answers4

Linked