sqlserver 字符串多行合并为一行

--创建测试表IF OBJECT_ID('test') IS NOT NULL        DROP TABLE testCREATE TABLE dbo.test(       id int IDENTITY(1,1) NOT NULL,    name varchar(50) NULL,    subject varchar(50) NULL,    source decimal(18,2) NULL)GO

--插入测试数据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 '王五','英语',80GOSELECT * FROM test

--方法1：通过 select 累加DECLARE @sql_col VARCHAR(8000)SELECT @sql_col = ISNULL(@sql_col + ',','') + QUOTENAME(subject) FROM test GROUP BY subjectSELECT @sql_col

--方法2：通过 FOR xml path('') 合并字符串记录SELECT    STUFF(        (SELECT '#' + subject         FROM test         WHERE name = '王五'         FOR xml path('')        ),1,1,''        )

--方法3：分组合并字符串记录SELECT    name,    Subject = (               STUFF(                    (SELECT '#' + subject                     FROM test                     WHERE name = A.name                     FOR xml path('')                    ),1,1,''                    )                 )FROM test AGROUP by name