SQL Server实现类型Oracle 中类似Listogg的功能

select * from [dbo].[Team]
ID	TL	Member
1	Sherman	Sonia,Hilary,Clair
2	Alvin	Chris,Josie,Joanna
3	Nancy	Evelyn,Ann,Lena


IF OBJECT_ID('dbo.Team', 'U') IS NOT NULL DROP TABLE dbo.Team;  
CREATE TABLE dbo.Team(  
ID INT
, TL VARCHAR(30)
, Member VARCHAR(200)
)  
  
INSERT INTO Team VALUES  
(1,'Sherman','Sonia'),
(1,'Sherman','Hilary'),
(1,'Sherman','Clair'),
(2,'Alvin','Chris'),
(2,'Alvin','Josie'),
(2,'Alvin','Joanna'),
(3,'Nancy','Evelyn'),
(3,'Nancy','Ann'),
(3,'Nancy','Lena');

--分组拼接字符串是子查询中连接条件必含外层GROUP BY限定的字段(如果外层使用GROUP BY)

SELECT t.Id
    , t.TL
    , COALESCE(
                STUFF(
                      (SELECT ',' + CAST(Member AS VARCHAR(10)) AS [text()]
                         FROM Team AS O
                       WHERE O.Id = t.Id
                       ORDER BY Member
                       FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),
                      1, 1, ''
                      )
              , '') AS Member
FROM Team t
GROUP BY t.Id, t.TL