Как можно объединить строки в предложении GROUP BY без подзапроса в SQL Server без дополнительного запроса?

Question

Я ищу эквивалент функции MySQL GROUP_CONCAT() в SQL Server 2012 - который не использует подпись, объяснено ниже:

CREATE TABLE Temp
( 
ID INT PRIMARY KEY NOT NULL IDENTITY(1,1),
ColA varchar(900) NULL,
ColB varchar(900) NULL
)

INSERT INTO Temp (ColA, ColB)
SELECT 'A', 'some' UNION ALL
SELECT 'A', 'thing' UNION ALL
SELECT 'A', 'and' UNION ALL
SELECT 'B', 'some' UNION ALL
SELECT 'B', 'more' UNION ALL
SELECT 'B', 'and' UNION ALL
SELECT 'B', 'more' UNION ALL
SELECT 'C', 'things' UNION ALL
SELECT 'C', 'things'

-- Desired Output. Note that the lists are in descending order of frequency ('more' appears twice)
ColA, Frequency, ColBs
'B', 4, 'more, some, and'
'A', 3, 'some, thing, and'
'C', 2, 'things'

SELECT 
    ColA, 
    COUNT(*) as Frequency, 
    GROUP_CONCAT(ColB) --Would be nice
FROM Temp
GROUP BY ColA
ORDER BY Frequency DESC

Общий ответ на это в SQL Server - использование STUFF() в подзапросе. В моем случае производительность просто неприемлема (200 миллионов записей, 26 секунд на подзапрос * 200 миллионов = 164 года).

SELECT 
    ColA, 
    COUNT(*) as Frequency, 
    ISNULL(
        STUFF((
            SELECT ', ' + ColBs FROM
                (SELECT ColBs, Count(*) as Frequency
                FROM Temp sub
                WHERE sub.ColA = t.ColA
                GROUP BY ColB
                ORDER BY Frequency DESC)
            FOR XML PATH('')
        ), 1, 2, '')
    ), '') as ColBs --Would take 164 years on the entire data set
FROM Temp t
GROUP BY ColA
ORDER BY Frequency DESC

Желаемым выходным значением являются значения ColB для каждого уникального ColA, сгруппированные вместе и в порядке убывания, как показано выше. Тем не менее, это должно быть сделано с помощью одного запроса через таблицу.

Нужно ли создавать это самому и отказаться от вызова "GROUP BY"? Перебирать набор данных вручную и создавать новую таблицу в консольном приложении? Или мне чего-то не хватает?

Answer 1

Попробуй это:

WITH prelim
AS
(
   SELECT
     cola
    ,colb
    ,count(*) AS recs
    ,row_number() over (partition BY cola ORDER BY count(*) DESC ,colb) AS recno
    ,Count(*) over (partition BY cola ) AS cnt
  FROM TEMP
  GROUP BY cola,colb ),
Group_Concat (recno,cnt,recs,cola,colbs)
AS
(
SELECT
    recno
    ,cnt
    ,recs
    ,cola
    ,CAST (colb AS varchar(MAX)) AS colbs
FROM
    prelim
WHERE
    recno=1
UNION ALL
SELECT
    p.recno
    ,p.cnt
    ,g.recs+p.recs
    ,p.cola
    , g.colbs + ', ' + CAST (p.colb AS varchar(MAX)) AS colbs
FROM
    prelim p
    JOIN Group_Concat g ON p.cola=g.cola AND p.recno=g.recno+1
)

SELECT COLA,Recs as Frequency,COLBS 
FROM Group_Concat
where recno=cnt
order by cola