------将一张表中的数据根据某行数据合并,并将另一行数据以逗号分隔合并--------
--建测试表
CREATE TABLE test(id int, VALUE NVARCHAR(30))
--插入测试数据
INSERT INTO test(id,value)VALUES(1,'aa')
INSERT INTO test(id,value)VALUES(1,'bb')
INSERT INTO test(id,value)VALUES(1,'cc')
INSERT INTO test(id,value)VALUES(2,'cc')
INSERT INTO test(id,value)VALUES(2,'cc')
INSERT INTO test(id,value)VALUES(3,'cc')
INSERT INTO test(id,value)VALUES(3,'cc')
SELECT id,value FROM dbo.test
-----需求为将同一个id的values合并以逗号分隔。
-----使用自连接和for xml path('')
--建测试表
CREATE TABLE test(id int, VALUE NVARCHAR(30))
--插入测试数据
INSERT INTO test(id,value)VALUES(1,'aa')
INSERT INTO test(id,value)VALUES(1,'bb')
INSERT INTO test(id,value)VALUES(1,'cc')
INSERT INTO test(id,value)VALUES(2,'cc')
INSERT INTO test(id,value)VALUES(2,'cc')
INSERT INTO test(id,value)VALUES(3,'cc')
INSERT INTO test(id,value)VALUES(3,'cc')
SELECT id,value FROM dbo.test
-----需求为将同一个id的values合并以逗号分隔。
-----使用自连接和for xml path('')
SELECT id,valuelist=(SELECT ','+value FROM test a WHERE a.id=b.id FOR XML PATH('')) FROM test b GROUP BY id
SELECT id,valuelist=STUFF((SELECT ','+value FROM test a WHERE a.id=b.id FOR XML PATH('')),1,1,'') FROM test b GROUP BY id