SQL多行变一列
CREATE TABLE DEPT
(DeptNo INT IDENTITY(1, 1)NOT NULL ,
Country VARCHAR(50) ,
Location VARCHAR(50) NULL
)
SET IDENTITY_INSERT DEPT ON
INSERT DEPT( DeptNo, Country, Location )VALUES ( 1, 'User1', 'A' )
INSERT DEPT( DeptNo, Country, Location )VALUES ( 2, 'User1', 'B' )
INSERT DEPT( DeptNo, Country, Location )VALUES ( 3, 'User2', 'C' )
INSERT DEPT( DeptNo, Country, Location )VALUES ( 4, 'User2', 'D' )
INSERT DEPT( DeptNo, Country, Location )VALUES ( 5, 'User2', 'E' )
INSERT DEPT( DeptNo, Country, Location )VALUES ( 6, 'User3', 'F' )
INSERT DEPT( DeptNo, Country, Location )VALUES ( 7, 'User3', 'G' )
INSERT DEPT( DeptNo, Country, Location )VALUES ( 8, 'User3', 'H' )
INSERT DEPT( DeptNo, Country, Location )VALUES ( 9, 'User3', 'I' )
SET IDENTITY_INSERT DEPT OFF
--按某一列出结果的
SELECT B.Country ,LEFT(Location, LEN(Location) - 1) AS list
FROM ( SELECT Country ,
( SELECT Location + ',' FROM DEPT WHERE Country = A.Country ORDER BY DeptNo FOR XML PATH('')
) AS Location
FROM DEPT A GROUP BY Country
) B
作者:Jackhuclan
出处:http://jackhuclan.cnblogs.com/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。
出处:http://jackhuclan.cnblogs.com/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。