Действительно ли возможно связать значения столбцов в строку с помощью CTE?
Скажите, что у меня есть следующая таблица:
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
Я играл вокруг с ним немного, но не смог получить его работа. Я сделал бы лучшее использование другой техники?
2 ответа
Я не рекомендую это, но мне удалось это решить.
Таблица:
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
CTE - это просто прославленная производная таблица с некоторыми дополнительными функциями (например, рекурсией). Вопрос в том, можно ли для этого использовать рекурсию? Наверное, но гвоздь забивают отверткой. Приятная часть создания пути XML (показанного в первом ответе) заключается в том, что он объединяет группировку столбца MyId с конкатенацией строк.
Как бы вы объединили список строк с помощью CTE? Я не думаю, что это его цель.