sqlserver 字符串多行合并为一行

1、--创建测试表IF OBJECT_ID('test') IS NOT NUL雉搽妤粲L 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

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 '王五','英语',80GOSELECT * FROM test

3、--方法1：通过 select 累加DECLARE @sql_col VARCHAR(8000)SELECT @sql_col = ISNULL(@sql_col + ',','') + QUOTENAME(subject) FROM test GROUP BY subjectSELECT @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 AGROUP by name