如何将列值转换为逗号分隔的行值
我有一张表,其值为
FKTABLE_NAME FKCOLUMN_NAME PKCOLUMN_NAME
table1 column1 column1
table1 column2 column2
table2 column1 column1
table2 column2 column2
我需要如何将其转换为
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.
谢谢
推荐答案
这是对任何数据库的有效查询
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
这是您的查询
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
相关文章