mssql sqlserver 将表多行数据转换为一行数据方法分享


摘要:
下文讲述使用sql脚本将表多行数据,分组变为一行,并将不同行数据的值采用逗号分隔,如下所示:
实验环境:sqlserver 2005


例:

  

CREATE TABLE test (keyId INT, sort VARCHAR(20), info VARCHAR(20))
GO
INSERT INTO test (keyId, sort, info)
SELECT 1, 'A', 'B'
UNION ALL
SELECT 1, 'wuhan', 'hubei'
UNION ALL
SELECT 2, 'shijiazhuang', 'hebei'
UNION ALL
SELECT 2, 'taiyuan', 'anhui'
UNION ALL
SELECT 2, 'chengdou', 'sichuan'
UNION ALL
SELECT 3, 'shenzhen', 'guangdong'
GO
 
----使用xmlPath连接sort 和info 根据keyId分组,
 
SELECT
keyId,
STUFF((
SELECT ', ' + sort + ' |' + info
FROM test
WHERE (keyId = a.keyId)
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,2,'') AS infoAll
FROM test a
GROUP BY keyId
GO

go

truncate table test
drop table test