Concatenação de várias colunas

Fiz alguns testes usando um pouco mais de 6 mil linhas. Com um índice na coluna ID.

Aqui está o que eu descobri.

Sua consulta inicial:

SELECT * FROM (
    SELECT t.id,
            stuff([M].query('/name').value('/', 'varchar(max)'),1,1,'') AS [SomeField_Combined1],
            stuff([M].query('/car').value('/', 'varchar(max)'),1,1,'') AS [SomeField_Combined2]
    FROM dbo.test t
    OUTER APPLY(SELECT (
                    SELECT id, ','+name AS name
                    ,','+car AS car
                    FROM test WHERE test.id=t.id
                    FOR XML PATH('') ,type)
                 AS  M) 
            M ) S
GROUP BY id, SomeField_Combined1, SomeField_Combined2 

Este funcionou por ~ 23 minutos.

Eu corri esta versão que é a versão que aprendi pela primeira vez. De certa forma, parece que deveria demorar mais, mas não demora.

SELECT test.id,
    STUFF((SELECT ', ' + ThisTable.name
            FROM   test ThisTable
            WHERE  test.id = ThisTable.id
            AND    ThisTable.name <> ''
            FOR XML PATH ('')),1,2,'') AS ConcatenatedSomeField,
    STUFF((SELECT ', ' + car
            FROM   test ThisTable
            WHERE  test.id = ThisTable.id
            AND    ThisTable.car <> ''
            FOR XML PATH ('')),1,2,'') AS ConcatenatedSomeField2
FROM test 
GROUP BY id

Esta versão durou pouco mais de 2 minutos.

Um agregado CLR quase certamente será a maneira mais rápida de fazer isso. Mas talvez você não queira usar um por qualquer motivo...

Você diz que a fonte para isso é uma consulta cara.

Eu materializaria isso em uma #temptabela primeiro para garantir que ela seja avaliada apenas uma vez.

CREATE TABLE #test
(
ID INT,
name NVARCHAR(128),
car  NVARCHAR(128)
);

CREATE CLUSTERED INDEX ix ON #test(ID);

O plano de execução que obtenho para a consulta na pergunta primeiro faz a concatenação para cada linha na consulta externa e, em seguida, remove as duplicatas por id, SomeField_Combined1, SomeField_Combined2.

Isso é incrivelmente um desperdício. A reescrita a seguir evita isso.

SELECT t.id,
       stuff([M].query('/name').value('/', 'varchar(max)'), 1, 1, '') AS [SomeField_Combined1],
       stuff([M].query('/car').value('/', 'varchar(max)'), 1, 1, '')  AS [SomeField_Combined2]
FROM   (SELECT DISTINCT id
        FROM   #test) t
       OUTER APPLY(SELECT (SELECT id,
                                  ',' + name AS name,
                                  ',' + car  AS car
                           FROM   #test
                           WHERE  #test.id = t.id
                           FOR XML PATH(''), type) AS M) M 

No entanto, para os seguintes dados de teste (1000 ids com 2156 linhas por id para mim)

INSERT INTO #test
SELECT v.number, o.name, o.type_desc
FROM   sys.all_objects o
       INNER JOIN master..spt_values v
         ON v.type = 'P' AND v.number BETWEEN 1 AND 1000 

Ainda encontrei a solução de Kenneth com duas XML PATHchamadas muito mais rápida e com menos recursos.

+-----------------+--------------------+------------------------+------------------+---------------------+-------------------------+-----------------------------+
|                 | CPU Time (Seconds) | Elapsed Time (Seconds) | #test Scan Count | #test Logical Reads | Worktable logical reads | Worktable lob logical reads |
+-----------------+--------------------+------------------------+------------------+---------------------+-------------------------+-----------------------------+
| Single XML PATH | 51.077             | 15.521                 | 1,005            | 60,165              | 51,161                  | 1,329,207                   |
| Double XML PATH | 3.1720             | 3.010                  | 2,005            | 92,088              | 14,951                  |   233,681                   |
+-----------------+--------------------+------------------------+------------------+---------------------+-------------------------+-----------------------------+

Para cada distinto id, #testele executa duas operações em vez de uma, mas essa operação é significativamente mais barata do que construir o XML e depois analisá-lo novamente.

Como já apontado por Martin Smith, um agregado CLR é provavelmente sua melhor aposta. Novamente, armazenar seus resultados em uma tabela temporária é uma boa ideia.

Aqui está outra possível implementação T-SQL que usa UNPIVOT/PIVOT:

IF OBJECT_ID('tempdb..#test') IS NOT NULL 
    DROP TABLE #test;

CREATE TABLE #test (
    id int,
    name varchar(128),
    car varchar(128)
)

CREATE CLUSTERED INDEX ix ON #test(ID);

INSERT INTO #test
SELECT v.number, o.name, o.type_desc
FROM   sys.all_objects o
       INNER JOIN master..spt_values v
         ON v.type = 'P' AND v.number BETWEEN 1 AND 1000 

;WITH info(col) AS (
    SELECT 'car'
    UNION ALL
    SELECT 'name'
)
SELECT *
FROM info
CROSS JOIN (
    SELECT DISTINCT id
    FROM #test
) AS ids
CROSS APPLY (
    SELECT v = STUFF((
        SELECT ',' + value AS [text()]
        FROM #test
        UNPIVOT (value FOR col IN (name,car)) AS u
        WHERE col = info.col
            AND id = ids.id
            AND value <> ''
        FOR XML PATH(''), type
    ).value('.','varchar(max)'),1,1,SPACE(0))
) AS ca(val)
PIVOT (MIN(val) FOR col IN (car,name)) AS p;

Ele é executado aproximadamente no mesmo tempo que a solução de Kenneth.

Tente isso

Use Righta função para remover a vírgula inicial em vez de xmlfunções

Use caseinstruções para evitar vírgulas para espaços em branco

SELECT t.id, 
       RIGHT(A.NAME, Len(A.NAME) - 1) AS NAME, 
       RIGHT(A.car, Len(A.car) - 1)   AS car 
FROM   dbo.test t 
       OUTER apply(SELECT (SELECT id, 
                                  CASE WHEN NAME<>'' THEN ',' ELSE '' END + NAME  AS NAME, 
                                  CASE WHEN car<>'' THEN ',' ELSE '' END + car AS car 
                           FROM   test 
                           WHERE  test.id = t.id 
                           FOR xml path(''), type) AS M) A 
GROUP  BY id, 
          RIGHT(A.NAME, Len(A.NAME) - 1), 
          RIGHT(A.car, Len(A.car) - 1) 

Nota: Aqui Group bytambém pode ser substituído por distinct, pois não estamos usando nenhuma aggregatefunção