说我有下表:
id|myId|Name ------------- 1 | 3 |Bob 2 | 3 |Chet 3 | 3 |Dave 4 | 4 |Jim 5 | 4 |Jose -------------
是否可以使用递归CTE生成以下输出:
3 | Bob, Chet, Date 4 | Jim, Jose
我已经试了一下,但是还没能使它正常工作。我会使用其他技术做得更好吗?
我不建议这样做,但是我设法解决了。
桌子:
CREATE TABLE [dbo].[names]( [id] [int] NULL, [myId] [int] NULL, [name] [char](25) NULL ) ON [PRIMARY]
数据:
INSERT INTO names values (1,3,'Bob') INSERT INTO names values 2,3,'Chet') INSERT INTO names values 3,3,'Dave') INSERT INTO names values 4,4,'Jim') INSERT INTO names values 5,4,'Jose') INSERT INTO names values 6,5,'Nick')
询问:
WITH CTE (id, myId, Name, NameCount) AS (SELECT id, myId, Cast(Name AS VARCHAR(225)) Name, 1 NameCount FROM (SELECT Row_number() OVER (PARTITION BY myId ORDER BY myId) AS id, myId, Name FROM names) e WHERE id = 1 UNION ALL SELECT e1.id, e1.myId, Cast(Rtrim(CTE.Name) + ',' + e1.Name AS VARCHAR(225)) AS Name, CTE.NameCount + 1 NameCount FROM CTE INNER JOIN (SELECT Row_number() OVER (PARTITION BY myId ORDER BY myId) AS id, myId, Name FROM names) e1 ON e1.id = CTE.id + 1 AND e1.myId = CTE.myId) SELECT myID, Name FROM (SELECT myID, Name, (Row_number() OVER (PARTITION BY myId ORDER BY namecount DESC)) AS id FROM CTE) AS p WHERE id = 1
根据要求,这是XML方法:
SELECT myId, STUFF((SELECT ',' + rtrim(convert(char(50),Name)) FROM namestable b WHERE a.myId = b.myId FOR XML PATH('')),1,1,'') Names FROM namestable a GROUP BY myId