SQL 合并多条记录为一条

-- 示例数据
DECLARE@tTABLE(id int, value varchar(10))
INSERT@tSELECT1, 'aa'
UNIONALLSELECT1, 'bb'
UNIONALLSELECT2, 'aaa'
UNIONALLSELECT2, 'bbb'
UNIONALLSELECT2, 'ccc'

-- 查询处理
SELECT*
FROM(
    SELECTDISTINCT
        id
    FROM@t
)A
OUTER APPLY(
    SELECT
        [values]=STUFF(REPLACE(REPLACE(
            (
                SELECT value FROM@t N
                WHERE id = A.id
                FOR XML AUTO
            ), '<N value="', ','), '"/>', ''), 1, 1, '')
)N

/*--结果
id          values
----------- ----------------
1           aa,bb
2           aaa,bbb,ccc
(2 行受影响)
--*/

--各种字符串分函数

--3.3.1 使用游标法进行字符串合并处理的示例。
--处理的数据
CREATETABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT'a',1
UNIONALLSELECT'a',2
UNIONALLSELECT'b',1
UNIONALLSELECT'b',2
UNIONALLSELECT'b',3

--合并处理
--定义结果集表变量
DECLARE@tTABLE(col1 varchar(10),col2 varchar(100))

--定义游标并进行合并处理
DECLARE tb CURSOR LOCAL
FOR
SELECT col1,col2 FROM tb ORDERBY  col1,col2
DECLARE@col1_oldvarchar(10),@col1varchar(10),@col2int,@svarchar(100)
OPEN tb
FETCH tb INTO@col1,@col2
SELECT@col1_old=@col1,@s=''
WHILE@@FETCH_STATUS=0
BEGIN
    IF@col1=@col1_old
        SELECT@s=@s+','+CAST(@col2asvarchar)
    ELSE
    BEGIN
        INSERT@tVALUES(@col1_old,STUFF(@s,1,1,''))
        SELECT@s=','+CAST(@col2asvarchar),@col1_old=@col1
    END
    FETCH tb INTO@col1,@col2
END
INSERT@tVALUES(@col1_old,STUFF(@s,1,1,''))
CLOSE tb
DEALLOCATE tb
--显示结果并删除测试数据
SELECT*FROM@t
DROPTABLE tb
/*--结果
col1       col2
---------- -----------
a          1,2
b          1,2,3
--*/
GO


/*==============================================*/


--3.3.2 使用用户定义函数，配合SELECT处理完成字符串合并处理的示例
--处理的数据
CREATETABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT'a',1
UNIONALLSELECT'a',2
UNIONALLSELECT'b',1
UNIONALLSELECT'b',2
UNIONALLSELECT'b',3
GO

--合并处理函数
CREATEFUNCTION dbo.f_str(@col1varchar(10))
RETURNSvarchar(100)
AS
BEGIN
    DECLARE@revarchar(100)
    SET@re=''
    SELECT@re=@re+','+CAST(col2 asvarchar)
    FROM tb
    WHERE col1=@col1
    RETURN(STUFF(@re,1,1,''))
END
GO

--调用函数
SELECT col1,col2=dbo.f_str(col1) FROM tb GROUPBY col1
--删除测试
DROPTABLE tb
DROPFUNCTION f_str
/*--结果
col1       col2
---------- -----------
a          1,2
b          1,2,3
--*/
GO

/*==============================================*/


--3.3.3 使用临时表实现字符串合并处理的示例
--处理的数据
CREATETABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT'a',1
UNIONALLSELECT'a',2
UNIONALLSELECT'b',1
UNIONALLSELECT'b',2
UNIONALLSELECT'b',3

--合并处理
SELECT col1,col2=CAST(col2 asvarchar(100))
INTO #t FROM tb
ORDERBY col1,col2
DECLARE@col1varchar(10),@col2varchar(100)
UPDATE #t SET
    @col2=CASEWHEN@col1=col1 THEN@col2+','+col2 ELSE col2 END,
    @col1=col1,
    col2=@col2
SELECT*FROM #t
/*--更新处理后的临时表
col1       col2
---------- -------------
a          1
a          1,2
b          1
b          1,2
b          1,2,3
--*/
--得到最终结果
SELECT col1,col2=MAX(col2) FROM #t GROUPBY col1
/*--结果
col1       col2
---------- -----------
a          1,2
b          1,2,3
--*/
--删除测试
DROPTABLE tb,#t
GO


/*==============================================*/

--3.3.4.1 每组 <=2 条记录的合并
--处理的数据
CREATETABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT'a',1
UNIONALLSELECT'a',2
UNIONALLSELECT'b',1
UNIONALLSELECT'b',2
UNIONALLSELECT'c',3

--合并处理
SELECT col1,
    col2=CAST(MIN(col2) asvarchar)
        +CASE
            WHENCOUNT(*)=1THEN''
            ELSE','+CAST(MAX(col2) asvarchar)
        END
FROM tb
GROUPBY col1
DROPTABLE tb
/*--结果
col1       col2     
---------- ----------
a          1,2
b          1,2
c          3
--*/

--3.3.4.2 每组 <=3 条记录的合并
--处理的数据
CREATETABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT'a',1
UNIONALLSELECT'a',2
UNIONALLSELECT'b',1
UNIONALLSELECT'b',2
UNIONALLSELECT'b',3
UNIONALLSELECT'c',3

--合并处理
SELECT col1,
    col2=CAST(MIN(col2) asvarchar)
        +CASE
            WHENCOUNT(*)=3THEN','
                +CAST((SELECT col2 FROM tb WHERE col1=a.col1 AND col2 NOTIN(MAX(a.col2),MIN(a.col2))) asvarchar)
            ELSE''
        END
        +CASE
            WHENCOUNT(*)>=2THEN','+CAST(MAX(col2) asvarchar)
            ELSE''
        END
FROM tb a
GROUPBY col1
DROPTABLE tb
/*--结果
col1       col2
---------- ------------
a          1,2
b          1,2,3
c          3
--*/
GO
ifnotobject_id('A') isnull
    droptable A
Go
Createtable A([id]int,[cname]nvarchar(2))
Insert A
select1,N'张三'unionall
select2,N'李四'unionall
select3,N'王五'unionall
select4,N'蔡六'
Go
--> -->

ifnotobject_id('B') isnull
    droptable B
Go
Createtable B([id]int,[cname]nvarchar(5))
Insert B
select1,N'1,2,3'unionall
select2,N'3,4'
Go
createfunction F_str(@cnamenvarchar(100))
returnsnvarchar(100)
as
begin
select@cname=replace(@cname,ID,[cname]) from A wherepatindex('%,'+rtrim(ID)+',%',','+@cname+',')>0
return@cname
end
go
select[id],dbo.F_str([cname])[cname]from B

id          cname
----------- ----------------------------------------------------------------------------------------------------
1           张三,李四,王五
2           王五,蔡六