Concatenando Valores na Mesma Linha – T-SQL

Galera, boa tarde. Este post é dedicado ao meu amigo Caio Vinicius. Ele só tem um problema: Programa em Java…rs.Mais o mano é firmeza. Vamos ao post! Colocando a mão na massa.

Estou trazendo hoje uma solução que apliquei em uma procedure hoje aqui na empresa. “Teoricamente” o problema poderia ser resolvido por um loop. Essa foi a primeira idéia do desenvolvedor,mas , pedi para ele que tivesse paciência para ambos pensarmos em uma solução em lote(Além de performática é mais elegante). Uma vez li que: Qualquer instrução que é feito linha-a-linha pode ser modificada para uma instrução “set-based” By Itzik.

Vamos Ao Cenário:
Em um ambiente OLTP, você tem uma tabela PAI e uma tabela FILHO em um relacionamento “1:N”. Um pai pode ter um ou mais filhos. O print abaixo mostra a estrutura das tabelas.

Como pode ser visto a Id 1(“X”) é Pai dos registros 1,2 e 3(A,B e C) na tabela filho (Contorno preto). E o Id 2 (y) é pai dos registros 4 e 5 (D e E) na tabela filho(Contorno Vermelho).

Um amigo desenvolvedor gostaria de realizar uma query deveria trazer o registro Pai em uma linha juntamente com o nome dos filhos concatenados na mesma linha. O resultado seria algo mais o menos assim:

Sabiamos que para não realizar um loop, poderíamos usar CTEs para resolver nosso problema, então fomos navegar na net até que achamos os caminhos das pedras (Referências no final do artigo).

Vamos ao código:
Primeiramente crio as tabelas #pai e #filho com os registros que foram ilustrados no print das estruturas da tabela.

Use tempdb
go

create table #pai(id int, descricao varchar(50))
create table #filho(id int, descricao varchar(50),id_pai int)

insert into #pai(id,descricao)values(1,’X’),(2,’Y’)
insert into #filho(id,descricao,id_pai)values(1,’A’,1),(2,’B’,1),(3,’C’,1)
insert into #filho(id,descricao,id_pai)values(4,’D’,2),(5,’E’,2)

Neste exeplo foram criadas três CTEs para resolver o problema.

;WITH RangeTabelaFilho ( PaiID, rnk, Filho )
AS (
      SELECT    f.id_pai as IdPai,
                ROW_NUMBER() OVER(PARTITION BY f.id_pai ORDER BY f.id_pai ) as IncrementoPorIdPai,
                CAST(f.descricao AS VARCHAR(8000))
         FROM #filho f
         join #pai p
         on f.id_pai = p.id
    )

Dentro desta CTE é feito um select da tabela #filho e da tabela #pai. A função ROW_NUMBER com a claúsula  Partition By diz ao SQL Server o seguinte: Cria um valor incremental inteiro e quando o idPai “mudar” recomeça a contagem. Esse comportamento pode ser visto nas linhas contornadas do print anterior.

2º – A segunda CTE chamada “AncoraDoRank” faz uma consulta na primeira CTE apenas filtrando os membros ancoras que são os registros com o rnk = 1. Perceba que as ancoras da consulta são os registros contornados no
primeiro print:

,AncoraDoRank (PaiID, rnk, Filho)
AS
(
            SELECT PaiID,rnk,Filho
            FROM RangeTabelaFilho        WHERE rnk = 1
)

3º – A terceira CTE chamada “RecursoRanqueamento” é uma CTE recursiva que é feita entre as duas primeiras CTEs já citadas anteriormente. A CTE recursiva usa a CTE “AncoraDoRank” para pegar os membros ancoras. E faz a recursividade com a CTE “RangeTabelaFilho” e a própria “RecursoRanqueamento”. O resultado é o seguinte:

,RecursoRanqueamento (PaiID, rnk, Filho)
AS
(

        SELECT PaiID , rnk, Filho
        FROM AncoraDoRank
        UNION ALL
        SELECT    RangeTabelaFilho.PaiID, RangeTabelaFilho.rnk,
                        RecursoRanqueamento.Filho + ‘, ‘ + RangeTabelaFilho.Filho
                  FROM RangeTabelaFilho
            INNER JOIN RecursoRanqueamento
            ON RangeTabelaFilho.PaiID = RecursoRanqueamento.PaiID
            AND RangeTabelaFilho.rnk = RecursoRanqueamento.rnk + 1
 )

Perceba que foram retornados cinco registros. Para cada “nivel” de rank o SQL Server criou a concatenação para cada leitura que ele passou dentro da recursividade.

Ex: Rank 1,2 e 3 do IdPai de numero 1,o resultado foi: Rnk 1 = A, Rnk 2 = A,B e Rnk 3 = A,B,C.
No final apenas faço um SELECT na CTE trazendo PaiID e realizando um MAX na coluna nome dos Filho, para que o resultado seja apenas duas linhas (PaiID 1 e 2). O resultado atende a necessidade anterior sem que seja necessário
o uso de um loop, cursor.  Abaixo Segue o script geral da solução:

Use tempdb
go

create table #pai(id int, descricao varchar(50))
create table #filho(id int, descricao varchar(50),id_pai int)

insert into #pai(id,descricao)values(1,’X’),(2,’Y’)
insert into #filho(id,descricao,id_pai)values(1,’A’,1),(2,’B’,1),(3,’C’,1)
insert into #filho(id,descricao,id_pai)values(4,’D’,2),(5,’E’,2)

;WITH RangeTabelaFilho ( PaiID, rnk, Filho )
AS (
      SELECT    f.id_pai as IdPai,
                ROW_NUMBER() OVER(PARTITION BY f.id_pai ORDER BY f.id_pai ) as IncrementoPorIdPai,
                CAST(f.descricao AS VARCHAR(8000))
         FROM #filho f
         join #pai p
         on f.id_pai = p.id
    )
,AncoraDoRank (PaiID, rnk, Filho)
AS
(
            SELECT PaiID,rnk,Filho
            FROM RangeTabelaFilho        WHERE rnk = 1
)
,RecursoRanqueamento (PaiID, rnk, Filho)
AS
(

        SELECT PaiID , rnk, Filho
        FROM AncoraDoRank
        UNION ALL
        SELECT    RangeTabelaFilho.PaiID, RangeTabelaFilho.rnk,
                        RecursoRanqueamento.Filho + ‘, ‘ + RangeTabelaFilho.Filho
                  FROM RangeTabelaFilho
            INNER JOIN RecursoRanqueamento
            ON RangeTabelaFilho.PaiID = RecursoRanqueamento.PaiID
            AND RangeTabelaFilho.rnk = RecursoRanqueamento.rnk + 1
 )

SELECT PaiID, MAX( Filho ) AS NomeFilhos FROM RecursoRanqueamento  GROUP BY PaiID;
DROP TABLE #filho
DROP TABLE #pai

Espero ter ajudado.

Referências:
http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79050
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/