SQL SERVER 数据库多行合并 1行

 

1、使用for xml path('') 和stuff 

Select *
Into #temp
From 
( 
select 'A' id,'aa' name
union all
select 'A' id,'bb' name
union all
select 'A' id,'cc' name
union all
select 'B' id,'aa' name
)a

Select * From #temp
--查询 
Select id, 
stuff((select ','+name from #temp where id=t.id for xml path('')),1,1,'') 
From #temp t 
Group by id

--查询 
Select  
stuff((select ','+name from #temp  for xml path('')),1,1,'') 
 

Drop Table #temp

 

2、使用字符串函数 STRING_AGG，这个函数是SQLSERVER 2017 版本之后才有的

SELECT STRING_AGG(ISNULL(Name,'N/A'), ',')  FROM #temp

 

补充

如：将某张表的所有字段合并为一行，以逗号隔开:    ID,Name,Post

SELECT name FROM SYSCOLUMNS WHERE ID=OBJECT_ID('DCTB') --查询某表的字段

①

DECLARE @listStr VARCHAR(MAX);
SELECT @listStr = ISNULL(@listStr+',','')+ name
FROM SYSCOLUMNS WHERE ID=OBJECT_ID('DCTB')  order by colorder
SELECT @listStr

②

select id,
name=STUFF((SELECT ','+name FROM SYSCOLUMNS t WHERE ID=OBJECT_ID('Work') order by colorder FOR XML PATH('')), 1, 1, '')  
from SYSCOLUMNS
WHERE ID=OBJECT_ID('Work')
GROUP BY id