合并多行查询数据到一行：使用自连接、FOR XML PATH('')、STUFF或REPLACE函数

示例表 tb 数据如下

id value ————— 1 aa 1 bb 2 aaa 2 bbb 2 ccc

第一种

SELECT id, [val]=( SELECT[value]+','FROM tb AS b WHERE b.id = a.id FOR XML PATH('') ) FROM tb AS a

第一种显示结果 1 aa,bb, 1 aa,bb, 2 aaa,bbb,ccc, 2 aaa,bbb,ccc, 2 aaa,bbb,ccc,

第二种

SELECT id, [val]=( SELECT[value]+','FROM tb AS b WHERE b.id = a.id FOR XML PATH('') ) FROM tb AS a GROUPBY id

第二种显示结果 1 aa,bb, 2 aaa,bbb,ccc,

第三种 （用STUFF函数替换掉首端的逗号）

SELECT id, [val]=STUFF( (SELECT','+[value]FROM tb AS b WHERE b.id = a.id FOR XML PATH('')) , 1 , 1 , '' ) FROM tb AS a GROUPBY id

第三种显示结果 1 aa,bb 2 aaa,bbb,ccc

STUFF 函数将字符串插入另一字符串。它在第一个字符串中从开始位置删除指定长度的字符；然后将第二个字符串插入第一个字符串的开始位置。 STUFF ( character_expression , start , length ,character_expression_insert )

第四种 （用REPLACE函数将所有空格替换成逗号）

SELECT id, [val]=REPLACE( (SELECT[value]AS[data()]FROM tb AS b WHERE b.id = a.id FOR XML PATH('')) , '', ',') FROM tb AS a GROUPBY id

结果与第三种一样。

解析：[data()] 这里据说是起到一个类似数组的作用，具体用法还要再查。

如果外围不用REPLACE函数包住，则返回的结果是 aaa bbb ccc ，每项之间有空格，所以最后用REPLACE函数将所有空格替换成逗号。

======

本人写的存储过程

USE [learn2]
GO
/****** Object:  StoredProcedure [dbo].[spread_UsersignUpGetPage]    Script Date: 11/26/2013 08:31:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[spread_UsersignUpGetPage](
    @StartRowIndex int,
    @EndRowIndex int,
    @UniqueName nvarchar(50),
    @CheckStatus bit,
    @Enddate datetime,
    @Startdate datetime,
    @OrderExp nvarchar(50)='',
    @IsCount tinyint
)

AS
SET NOCOUNT ON;
declare
    @SQL nvarchar(1024),
    @SQLWhere nvarchar(1024),
    @NeedsAnd bit;
set @SQL='';
set @SQLWhere='';
set @NeedsAnd=0;
if(@OrderExp='')
    set @OrderExp='AddTime DESC';
if(@UniqueName<>'')
begin
    set @SQLWhere='UniqueName like ''%''+@UniqueName+''%''';
    set @NeedsAnd=1;
end

if(@CheckStatus is not null)
begin
    if(@NeedsAnd=1)
        set @SQLWhere=@SQLWhere+' and ';
    set @SQLWhere=@SQLWhere+'CheckStatus='+cast(@CheckStatus as varchar(1));
    set @NeedsAnd=1;
end
if(@Startdate is not null)
begin
    if(@NeedsAnd=1)
        set @SQLWhere=@SQLWhere+' and ';
    set @SQLWhere=@SQLWhere+'datediff(d,AddTime,'''+convert(varchar(10),@Startdate,120)+''')<=0';
    set @NeedsAnd=1;
end
if(@EndDate is not null)
begin
    if(@NeedsAnd=1)
        set @SQLWhere=@SQLWhere+' and ';
    set @SQLWhere=@SQLWhere+'datediff(d,AddTime,'''+convert(varchar(10),@EndDate,120)+''')>=0';
    set @NeedsAnd=1;
end
if(@IsCount=1)
begin
    set @SQL='select count(Id) from dbo.Usersignup';
    if(@SQLWhere<>'')
        set @SQL=@SQL+' where '+@SQLWhere;
end
else if(@IsCount=2)--计算未审核多少人，单据
begin
--select count(Id) as CheckCount,STUFF((select ','+tag from UserSignup for XML path('')),1,1,'') as tags
--from UserSignup
    set @SQL='select count(Id) as CheckCount,STUFF((select '',''+tag from UserSignup where CheckStatus=0 for XML path('''')),1,1,'''') as tags from dbo.Usersignup where checkstatus=0';
    if(@SQLWhere<>'')
        set @SQL='select count(Id) as CheckCount,STUFF((select '',''+tag from UserSignup where CheckStatus=0 
        and '+@SQLWhere+' for XML path('''')),1,1,'''') as tags from dbo.Usersignup where checkstatus=0 
         and '+@SQLWhere; --单据数    
end
else if(@IsCount=3)--计算已审核多少人，单据
begin
    set @SQL='select count(Id) as CheckCount,STUFF((select '',''+tag from UserSignup where CheckStatus=1 for XML path('''')),1,1,'''') as tags from dbo.Usersignup where checkstatus=1';
    if(@SQLWhere<>'')
        set @SQL='select count(Id) as CheckCount,STUFF((select '',''+tag from UserSignup where CheckStatus=1 
        and '+@SQLWhere+' for XML path('''')),1,1,'''') as tags from dbo.Usersignup where checkstatus=1 and '+@SQLWhere; --单据数
end
else
begin
    set @SQL='with Temp AS(select Row_Number() over(order by '+@OrderExp+') as RowId,Id from dbo.usersignUp';
    if(@SQLWhere<>'')
        set @SQL=@SQL+' where '+@SQLWhere;
    set @SQL=@SQL+')select Temp.RowId,A.Id,UniqueName,tag,A.addtime,userid,username,A.CheckStatus,A.description,groupid,B.ClassName as groupName from Temp join dbo.usersignUp A on Temp.Id=A.Id left join dbo.[group] B on A.groupid=B.ClassId where Temp.RowId between '+Cast(@StartRowIndex as varchar(10))+' and '+Cast(@EndRowIndex as varchar(10))+' order by Temp.RowId;';
end
EXEC sp_executeSql @SQL,
    N'@UniqueName nvarchar(50)',
    @UniqueName
WITH RECOMPILE

上面的存储过程 利用了上边提到的知识  stuff  for xml 。