CROSS APPLY com FOR XML, dentro de um FOR XML

Esses dias surgiu uma questão aqui no trabalho que acho que vale o registro…

Me questionaram sobre a possibilidade de concatenar o resultado de diversas linhas em uma única e, imediatamente, me veio o bom e velho FOR XML PATH(”) em mente.

O problema é que o resultado deveria ser concatenado de acordo com o resultado proveniente de uma outra tabela, algo como:

Status1 (Codigo1, Codigo2, Codigo3) Status 2 (Codigo4) Status 3 (Codigo5)

A criação da estrutura seria algo assim:

create table status (Codigo int identity(1,1) primary key, Descricao varchar(50) )
create table Produto (Codigo int identity(1,1) primary key, Nome varchar(50), Sigla char(5), CodigoProdutoPai int)
create table historico (Codigo int identity(1,1) primary key, CodigoPessoa int, CodigoProduto int, CodigoStatus int)

Abstraiam aqui qualquer boa prática ou regra de normalização… a ideia é mostrar a solução dada. 🙂

Inserindo alguns poucos registros…

insert into status values ('Ok'), ('Defeito'), ('Conserto'),('Testar')
insert into produto values ('produto 1', 'PROD1',null),('produto 2', 'PROD2',1),('produto 3', 'PROD3',null)
insert into historico values (1,1,4),(1,1,2),(1,1,1),(1,2,1),(2,3,1),(1,3,4),(1,3,1)

Com base nisso, vamos voltar à ideia…

Para cada status existente, eu preciso retornar os produtos que não possuem sub-produtos e que passaram por esse status, deixando os produtos entre parênteses.

A primeira ideia ao ler a descrição pode ser fazer usando um cursor, varrendo registro a registro e concatenando o valor.

Porém, ao invés de pensar por registro, vamos pensar diferente….

A consulta que vai retornar o que eu preciso inicialmente:

SELECT 
  STH.Descricao, PRD.Sigla
FROM Produto PRD
JOIN Historico HIS 
ON (HIS.CodigoProduto = PRD.Codigo) 
join Status STH
ON  (STH.Codigo = HIS.CodigoStatus)
WHERE 
  (HIS.CodigoPessoa = 1) AND 
  (PRD.CodigoProdutoPai IS NULL) 
ORDER BY descricao

O retorno será:

Agora, como deixar a sigla em uma linha, por descrição?

Para eu concatenar os registros, faço uso do FOR XML PATH… Assim:

SELECT 
  CONVERT(VARCHAR(100), TMP.Descricao + ' ' + TMP.Sigla + ', ' ) as [text()] 
FROM 
( 
    SELECT 
        STH.Descricao, DSS.Sigla
    FROM Produto DSS 
    JOIN Historico HSS 
        ON (HSS.CodigoProduto = DSS.Codigo) 
    JOIN Status STH
        ON(STH.Codigo = HSS.CodigoStatus)
    WHERE 
        (HSS.CodigoPessoa = 1) AND 
        (DSS.CodigoProdutoPai IS NULL) 
) AS TMP 
FOR XML PATH('')

Resultado:

Concatenou… Mas não era bem isso…

Como o que eu preciso é executar uma vez o FOR XML PATH para cada status existente, vamos usar o Operador APPLY (CROSS APPLY, no meu caso), que realiza esse comportamento, mas aí vamos ter que mudar algumas coisas na query.

Em tempo… Lembrem que a tabela que eu tenho como base é a Status e que é o retorno dos produtos que eu quero concatenar.

A consulta fica assim:

SELECT CONVERT(VARCHAR(100), STH.Descricao + ' (' + ltrim(temp.lista) + ' ) ' )  as [text()]
FROM status STH
CROSS APPLY (
  SELECT 
    CONVERT(VARCHAR(100), ', ' + TMP.Sigla ) as [text()] 
  FROM 
  ( 
    SELECT 
      DSS.Sigla
    FROM Produto DSS 
    JOIN Historico HSS 
      ON (HSS.CodigoProduto = DSS.Codigo) 
    WHERE 
      (STH.Codigo = HSS.CodigoStatus) AND
      (HSS.CodigoPessoa = 1) AND 
      (DSS.CodigoProdutoPai IS NULL) 
  ) AS TMP 
  FOR XML PATH('')
) temp (lista)
WHERE temp.lista IS NOT NULL

O resultado:

Opa! Estamos quase lá… Agora, basicamente, o que precisamos é reaplicar o FOR XML PATH nesse resultado e formatar a vírgula que ficou no primeiro parênteses, ficando:

DECLARE @Resultado varchar(200)  
SET @Resultado = 
(
  select * from 
    (
        SELECT CONVERT(VARCHAR(100), STH.Descricao + ' (' + ltrim(temp.lista) + ' ) ' )  as [text()]
        FROM status STH
        CROSS APPLY (
          SELECT 
            CONVERT(VARCHAR(100), ', ' + TMP.Sigla ) as [text()] 
          FROM 
          ( 
            SELECT 
              DSS.Sigla
            FROM Produto DSS 
            JOIN Historico HSS 
              ON (HSS.CodigoProduto = DSS.Codigo) 
            WHERE 
              (STH.Codigo = HSS.CodigoStatus) AND
              (HSS.CodigoPessoa = 1) AND 
              (DSS.CodigoProdutoPai IS NULL) 
          ) AS TMP 
          FOR XML PATH('')
        ) temp (lista)
        WHERE temp.lista IS NOT NULL
    ) AS tmp2
    FOR XML PATH('')
)
SELECT replace(@Resultado, '(,','(')

O que me retorna, finalmente:

Bom, talvez essa não seja a solução mais elegante ou performática… E é aí que começa a brincadeira… Como você faria? Adoraria ver outras soluções para o mesmo caso. 😀

Abraços e até uma próxima!

PS: Caso queira pegar o script com todo o passo a passo para executar na tua máquina, veja no meu gitHub: https://github.com/LoganMerazzi/SQLServerScripts/blob/master/FOR%20XML%20e%20APPLY