解析SQL Server 2005 溢用之：合并列值

示例数据 
DECLARE @t TABLE(id int, value varchar(10))

INSERT @t SELECT 1, "aa"

UNION ALL SELECT 1, "bb"

UNION ALL SELECT 2, "aaa"

UNION ALL SELECT 2, "bbb"

UNION ALL SELECT 2, "ccc"

 

-- 查询处理

SELECT *

FROM(

    SELECT DISTINCT 

        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 行受影响)

--*/