問題描述

我有一張表，其值為

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

這篇關于如何將列值轉換為逗號分隔的行值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！