quinta-feira, 25 de junho de 2009

Gerar registros em forma de colunas? Peça ajuda ao XML!

Bom dia pessoal.
Já vi muita pergunta em fóruns onde o pessoal vive tentando arranjar um jeito de mostrar uma série de registros em uma só coluna, separado por vírgula ou sei lá. Aproveitei uma thread que estava rolando no MSDN para usar de base para esse pequeno artigo...
O problema era pegar a consulta abaixo e retornar o resultado em somente uma linha:

USE MSDB
go

SELECT TOP 2 COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'backupset'

-- Consulta retorna:
backup_set_id
backup_set_uuid

-- Resultado desejado:
backup_set_id, backup_set_uuid


Você pode fazer isso com cursores ou inventar outra maluquice qualquer, mas não é nada elegante. Lendo um livro do Itzik Ben Gan eu vi uma abordagem bem elegante que ele propunha e passei a adotá-la em meus treinamentos e dicas.

A consulta que retorna o esperado pode ser escrita da seguinte forma:

SELECT STUFF(
(SELECT TOP 2
N',' + QUOTENAME(COLUMN_NAME) AS [text()]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'backupset'
FOR XML PATH('')), 1, 1, N'')
go


Aqui você utiliza o FOR XML PATH('') para gerar um XML sem elemento por registro e usa a função text() para recuperar somente o texto do elemento, dispensando as tags que seriam o nome da coluna. Depois é só remover a primeira vírgula e pronto!

Gostou da solução? Eu sim... :-)
Aqui está a thread do MSDN para consulta: http://social.msdn.microsoft.com/Forums/pt-BR/transactsqlpt/thread/35db803c-44ce-4007-8cef-9b36801d86dc/?prof=required

[]s
Luciano Caixeta Moreira - {Luti Nimbus}
Chief Innovation Officer
Sr. Nimbus Serviços em Tecnologia Ltda
E-mail: luciano.moreira@srnimbus.com.br

Um comentário:

  1. Fala Luti, só para complementar, tem um post no meu blog que, além de fazer "join" de strings, mostra como fazer o inverso (split), ou seja, valores em uma string separada por vírgula podem retornar em colunas. Tudo usando XML. Um grande abraço, JP.

    http://jpdotnet.blogspot.com/2009/05/split-e-join-de-strings-em-t-sql.html

    ResponderExcluir