如何将列值转换为逗号分隔的行值

问题描述

我有一张表，其值为

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