Мне нужен способ сделать конкатенацию всех строк (для каждой группы) в виде оконной функции, например, как вы можете сделать COUNT(*) OVER(PARTITION BY...), и подсчет совокупности всех строк на группу будет повторяться через каждую конкретную группу. Мне нужно нечто подобное, но строка конкатенации всех значений для каждой группы повторяется в каждой группе.
Вот некоторые примеры данных и мой желаемый результат, чтобы лучше проиллюстрировать мою проблему:
grp | val
------------
1 | a
1 | b
1 | c
1 | d
2 | x
2 | y
2 | z
И вот что мне нужно (желаемый результат):
grp | val | groupcnct
---------------------------------
1 | a | abcd
1 | b | abcd
1 | c | abcd
1 | d | abcd
2 | x | xyz
2 | y | xyz
2 | z | xyz
Моя конкретная ситуация мешает мне иметь возможность ссылаться на одну и ту же таблицу дважды (я на самом деле делаю это в рекурсивном CTE, поэтому я не могу самостоятельно присоединяться к CTE или это вызовет ошибку).
Я полностью понимаю, что можно сделать что-то вроде:
SELECT a.*, b.groupcnct
FROM tbl a
CROSS APPLY (
SELECT STUFF((
SELECT '' + aa.val
FROM tbl aa
WHERE aa.grp = a.grp
FOR XML PATH('')
), 1, 0, '') AS groupcnct
) b
Но, как вы можете видеть, это ссылается на tbl два раза в запросе.
Я могу ссылаться только на tbl один раз, поэтому мне интересно, возможно ли оконтирование групповой конкатенации (я немного новичок в TSQL, поскольку я исхожу из фона MySQL, поэтому не уверен, что что-то подобное можно сделать).
CREATE TABLE tbl
(grp int, val varchar(1));
INSERT INTO tbl
(grp, val)
VALUES
(1, 'a'),
(1, 'b'),
(1, 'c'),
(1, 'd'),
(2, 'x'),
(2, 'y'),
(2, 'z');
Я попытался использовать чистый подход CTE: Каков наилучший способ формирования строкового значения, используя столбец из таблицы с строками с одинаковым идентификатором? Думая, что это быстрее
Но в этом тесте указано, что лучше использовать подзапрос (или CROSS APPLY) результатов от XML PATH, поскольку они быстрее: Каков наилучший способ формирования строкового значения используя столбец из таблицы со строками с одинаковым идентификатором?
DECLARE @tbl TABLE
(
grp INT
,val VARCHAR(1)
);
BEGIN
INSERT INTO @tbl(grp, val)
VALUES
(1, 'a'),
(1, 'b'),
(1, 'c'),
(1, 'd'),
(2, 'x'),
(2, 'y'),
(2, 'z');
END;
----------- Your Required Query
SELECT ST2.grp,
SUBSTRING(
(
SELECT ','+ST1.val AS [text()]
FROM @tbl ST1
WHERE ST1.grp = ST2.grp
ORDER BY ST1.grp
For XML PATH ('')
), 2, 1000
) groupcnct
FROM @tbl ST2
Можно ли вместо этого просто поставить свои вещи в выбор или вы столкнулись с той же проблемой? (я заменил "tbl" на "TEMP.TEMP123")
Select
A.*
, [GROUPCNT] = STUFF((
SELECT '' + aa.val
FROM TEMP.TEMP123 AA
WHERE aa.grp = a.grp
FOR XML PATH('')
), 1, 0, '')
from TEMP.TEMP123 A
Это сработало для меня - хотел посмотреть, сработало ли это и для вас.
В sql 2017 вы можете использовать функцию STRING_AGG:
SELECT STRING_AGG(T.val, ',') AS val
, T.grp
FROM @tbl AS T
GROUP BY T.grp
Я знаю, что этот пост старый, но на всякий случай кому-то все еще интересно, вы можете создать скалярную функцию, которая объединяет значения строк.
IF OBJECT_ID('dbo.fnConcatRowsPerGroup','FN') IS NOT NULL
DROP FUNCTION dbo.fnConcatRowsPerGroup
GO
CREATE FUNCTION dbo.fnConcatRowsPerGroup
(@grp as int) RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @val AS VARCHAR(MAX)
SELECT @val = COALESCE(@val,'')+val
FROM tbl
WHERE grp = @grp
RETURN @val;
END
GO
select *, dbo.fnConcatRowsPerGroup(grp)
from tbl
Вот набор результатов, полученный при запросе таблицы-примера:
grp | val | (No column name)
---------------------------------
1 | a | abcd
1 | b | abcd
1 | c | abcd
1 | d | abcd
2 | x | xyz
2 | y | xyz
2 | z | xyz