sql按多行分组

Mahyar 发表于 Dev

马哈亚

我有这样的一张桌子：

Table: Subject_Selection

Subject   Semester   Attendee
---------------------------------
ITB001    1          John
ITB001    1          Bob
ITB001    1          Mickey
ITB001    2          Jenny
ITB001    2          James
MKB114    1          John
MKB114    1          Erica

我怎么有一个查询到此结果：

Subject   Semester   Attendee
---------------------------------
ITB001    1          John,Bob,Mickey    
ITB001    2          Jenny,James   
MKB114    1          John,Erica

马里

测试数据

DECLARE @TABLE TABLE ([Subject] VARCHAR(20),Semester INT,Attendee VARCHAR(20))
INSERT INTO @TABLE VALUES
('ITB001',1,'John'),('ITB001',1,'Bob'),('ITB001',1,'Mickey'),('ITB001',2,'Jenny')
,('ITB001',2,'James'),('MKB114',1,'John'),('MKB114',1,'Erica')

询问

SELECT   [Subject]  
       , Semester        
       , STUFF((SELECT ', ' + Attendee [text()]
               FROM @TABLE
               WHERE Semester = T.Semester
               AND [Subject] = T.[Subject]
               FOR XML PATH('')), 1, 2, '') AS Attendee
FROM @TABLE T
GROUP BY [Subject], Semester

Subject在SQL Server中使用的保留字[] square brackets周围

结果集

╔═════════╦══════════╦═══════════════════╗
║ Subject ║ Semester ║     Attendee      ║
╠═════════╬══════════╬═══════════════════╣
║ ITB001  ║        1 ║ John, Bob, Mickey ║
║ ITB001  ║        2 ║ Jenny, James      ║
║ MKB114  ║        1 ║ John, Erica       ║
╚═════════╩══════════╩═══════════════════╝

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。