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)

1	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)

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)

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

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('')

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

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, '(,','(')

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