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


摘要:
下文讲述sqlserver使用sql脚本将单列数据合并为一行的方法分享,如下所示:
实验环境:sql server 2008 R2

----基础数据准备
create table [maomao365.com]
(keyId int identity,
 info  nvarchar(30))
 go
 insert into [maomao365.com]
(info)values(N'sqlserver'),
(N'sql爱好者'),(N'sql教程'),
(N'猫猫小屋')
go
 
--方法一
declare @sql nvarchar(800);
set @sql = ''
select @sql=@sql+info+',' from [maomao365.com]
set @sql=left(@sql,len(@sql)-1)
select @sql as 结果1

--方法二
declare @sqltwo nvarchar(800)
select @sqltwo=coalesce(@sqltwo+',','')+[info] from [maomao365.com]
select @sqltwo as 结果2
 
---方法三
declare @sqlthree nvarchar(800)
select @sqlthree=isnull(@sqlthree+',','')+[info] from [maomao365.com]
select @sqlthree as 结果3
 
--方法四
select reverse(stuff(reverse((
select [info]+',' from [maomao365.com] for xml path(''))),1,1,'')) as 结果4

 go
 truncate table [maomao365.com]
 drop     table [maomao365.com]