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