问题描述
我有一张表,其值为
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