【收藏】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
posted @ 2017-07-26 03:56  lifuwa  阅读(250)  评论(0编辑  收藏  举报