T-SQL: CSV para Linhas (UDF e XML)

on others

Esta semana precisei de fazer exactamente o que o titulo do post indica, ou seja, retornar todas as linhas de uma tabela mas ao mesmo tempo separar os valores numa das colunas em cada linha.

Para testar, criei a seguinte tabela:

CREATE TABLE [dbo].[Agentes](
    [Codigo] [varchar](50) NULL,
    [Nome] [varchar](50) NULL,
    [Emails] [varchar](100) NULL
)

</p>

Inseri alguns dados com o mesmo formato da tabela real que iria depois utilizar:

INSERT INTO Agentes 
VALUES('C12345','Agente 1','[email protected];[email protected]')
INSERT INTO Agentes 
VALUES('C12346','Agente 2','[email protected];[email protected]')

Como já podem perceber, o campo “Emails” precisa de ser retornado com apenas um email, ou seja, preciso de passar uma listagem como esta

para uma como esta

Para isso precisei de fazer uma função que me separasse cada um emails de forma a poder retornar um por cada linha.


  CREATE FUNCTION dbo.SplitEmails(@separador char(1), 
@emails varchar(512))

RETURNS table
AS
RETURN (
    WITH Emails(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@separador, @emails)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@separador, @emails, 
stop + 1)
      FROM Emails
      WHERE stop > 0
    )
    SELECT SUBSTRING(@emails, start, CASE WHEN stop > 0 THEN 
stop-start ELSE 512 END) AS email
    FROM Emails
  )

Por fim, para ter a listagem com o formato pretendido, bastou fazer o CROSS APPLY com a minha tabela de agentes, e está o trabalho feito.

SELECT a.Codigo,a.Nome,c.email FROM Agentes a
CROSS APPLY SplitEmails(';',a.Emails) c

Outra forma de retornar esta listagem, indicada pelo @Caio, era recorrendo ao XML e evitava assim criar uma função para fazer o split dos emails.

WITH Consulta AS
(
SELECT Codigo, Nome,
CAST('<email>' + 
REPLACE(Emails, ';', '</email><email>') + 
'</email>' AS XML) 
AS EmailXml
FROM Agentes
)
 
SELECT Codigo, Nome,
    r.value('.', 'varchar(255)') AS Email
FROM
    Consulta
CROSS APPLY
    consulta.EmailXml.nodes('email') AS x(r)