sqlserver 字符串多行合并为一行

1、--创建测试表

IF OBJECT_ID('test') IS NOT NULL   

    DROP TABLE test

CREATE TABLE dbo.test(  

    id int IDENTITY(1,1) NOT NULL,

    name varchar(50) NULL,

    subject varchar(50) NULL,

    source decimal(18,2) NULL

)

GO

2、--插入测试数据

INSERT INTO test (name,subject,source)    

SELECT '张三','语文',60  UNION ALL   

SELECT '张三','英语',90  UNION ALL  

SELECT '李四','语文',70  UNION ALL   

SELECT '李四','数学',80  UNION ALL   

SELECT '王五','数学',75  UNION ALL   

SELECT '王五','英语',80

GO

SELECT * FROM test

3、--方法1：通过 select 累加

DECLARE @sql_col VARCHAR(8000)

SELECT @sql_col = ISNULL(@sql_col + ',','') + QUOTENAME(subject)

FROM test

GROUP BY subject

SELECT @sql_col

4、--方法2：通过 FOR xml path('') 合并字符串记录

SELECT

    STUFF(

        (SELECT '#' + subject

         FROM test

         WHERE name = '王五'

         FOR xml path('')

        ),1,1,''

        )

5、--方法3：分组合并字符串记录

SELECT

    name,

    Subject = (

               STUFF(

                    (SELECT '#' + subject

                     FROM test

                     WHERE name = A.name

                     FOR xml path('')

                    ),1,1,''

                    )

                 )

FROM test A

GROUP by name