- 10 Abril 2010
- Sem categoria
- 0 Comments
-
-
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
)
Inseri alguns dados com o mesmo formato da tabela real que iria depois utilizar:
INSERT INTO Agentes
VALUES('C12345','Agente 1','agente1@xpto.pt;agente1.loja@xpto.pt')
INSERT INTO Agentes
VALUES('C12346','Agente 2','agente2@xpto.pt;agente2.loja@xpto.pt')
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)
