mssql sqlserver 列数据合并的方法分享


摘要:
下文将分享对一张表的单列和多列合并为一列的方法分享,
sql 查出多条数据某个字段 合并一条逗号分隔 如何写
如下所示:
实验环境:sqlserver 2008


create table test(keyId int identity(1,1),
     infoA nvarchar(30),infoB nvarchar(30),infoC nvarchar(30)
	 )
go
---基础数据准备
insert into test(infoA,infoB,infoC)values('测试','maomao365','猫猫小屋')
insert into test(infoA,infoB,infoC)values('A','B','C')
insert into test(infoA,infoB,infoC)values('D','E','F') 
go

---方法1:直接使用列相加的方式
select keyId,isnull(infoA,'')+','+isnull(infoB,'')+','+isnull(infoC,'') from test 

---方法2: 采用xml 的方式
SELECT keyId,
(SELECT ','+isnull(infoA,'')+','+isnull(infoB,'')+','+isnull(infoC,'') FROM dbo.test WHERE keyId = t.keyId FOR XML PATH(''))
AS newInfo FROM dbo.test AS t

go

truncate table test 
drop     table test 
mssql_sqlserver_列数据合并

mssql_sqlserver_列数据合并