Registros separados por um caracter em Linha

Question

Pessoal, boa noite.

possuo neste código:

DECLARE @TESTE TABLE 
(COD INT, CONCATENADO VARCHAR(50))

INSERT INTO @TESTE (COD, CONCATENADO) VALUES (13, '5+3+1.5+4+8.75')
INSERT INTO @TESTE (COD, CONCATENADO) VALUES (21, '12.1+3+2')
INSERT INTO @TESTE (COD, CONCATENADO) VALUES (55, '3')

SELECT * FROM @TESTE;
	

O seguinte resultado:

como eu faço de uma tabela, retornar um SELECT da seguinte maneira?

Agradeço desde já....

abs

---

"Se esta sugestão for útil, por favor, classifique-a como útil.
Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta."

Érica Tohoma | http://bloggirlsa.wordpress.com

Answer 1

Boa noite,

Experimente desta forma:

SELECT 
    F1.COD,
    O.splitdata 
FROM
(
     SELECT 
         COD,
         cast('<X>' + replace(F.CONCATENADO, '+', '</X><X>') + '</X>' as XML) as xmlfilter 
     from @TESTE F
) F1
CROSS APPLY
( 
     SELECT 
         fdata.D.value('.', 'varchar(50)') as splitdata 
     FROM f1.xmlfilter.nodes('X') as fdata(D)
) O

fonte: http://www.mssqltips.com/sqlservertip/1771/splitting-delimited-strings-using-xml-in-sql-server/

Espero que ajude.

---

Assinatura: http://www.imoveisemexposicao.com.br

Answer 2

Oi Érica

Teste esse pra ver se te atende com as 3 colunas:

DECLARE @TESTE TABLE 
(COD INT, CONCATENADO VARCHAR(50))

INSERT INTO @TESTE (COD, CONCATENADO) VALUES (13, '5+3+1.5+4+8.75')
INSERT INTO @TESTE (COD, CONCATENADO) VALUES (21, '12.1+3+2')
INSERT INTO @TESTE (COD, CONCATENADO) VALUES (55, '3')

SELECT 
    F1.COD,
    ROW_NUMBER() OVER(PARTITION BY F1.COD ORDER BY F1.COD) AS TESTE,
    O.splitdata 
FROM
(
     SELECT 
         COD,
         cast('<X>' + replace(F.CONCATENADO, '+', '</X><X>') + '</X>' as XML) as xmlfilter 
     from @TESTE F
) F1
CROSS APPLY
( 
     SELECT 
         fdata.D.value('.', 'varchar(50)') as splitdata 
     FROM f1.xmlfilter.nodes('X') as fdata(D)
) O

---

Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

Answer 3

Bom dia Érica !

Voce ja pensou em utilizar uma temporaria para te auxiliar ? fazendo como abaixo cheguei ao modo que voce gostaria:

Create table #temp (COD INT, CONCATENADO VARCHAR(50))

DECLARE @TESTE TABLE 
(COD INT, CONCATENADO VARCHAR(50))

INSERT INTO @TESTE (COD, CONCATENADO) VALUES (13, '5+3+1.5+4+8.75')
INSERT INTO @TESTE (COD, CONCATENADO) VALUES (21, '12.1+3+2')
INSERT INTO @TESTE (COD, CONCATENADO) VALUES (55, '3')

--SELECT Cod, Concatenado, charindex('+', concatenado) FROM @TESTE

Declare @len int
declare @cod int
while (select count(*) from @TESTE where concatenado like '%+%') > 0
begin
set @cod = (select min(cod) from @TESTE where concatenado like '%') 
set @len = (select len(Concatenado) from @TESTE where cod = @cod)

if (select count(*) from @TESTE where concatenado like '%+%' and cod = @cod) > 0 
begin
insert into #temp
select Cod, substring(Concatenado,1,charindex('+', concatenado)) from @teste
where cod = @cod
end

if (select count(*) from @TESTE where concatenado not like '%+%' and cod = @cod and len(concatenado) > 0) > 0 
begin
insert into #temp
select Cod, concatenado from @teste
where cod = @cod

delete from @teste
where cod = @cod
end


update @teste
set concatenado = right(Concatenado, len(concatenado) - charindex('+', concatenado))
where cod = @cod

delete from @teste
where ltrim(rtrim(concatenado)) = ''
and cod = @cod 

set @cod = (select min(cod) from @TESTE where concatenado like '%') 
set @len = (select len(Concatenado) from @TESTE where cod = @cod)

end

if (select count(*) from @teste) > 0
begin
insert into #temp
select cod, concatenado from @teste
end


update #temp
set concatenado = replace(concatenado,'+','')
select * from #temp

---

Alexandre Matayosi Conde Mauricio. Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

Answer 4

Isto funcionaria no Sql 2005? Fiz um teste na minha máquina, sql 2008 e funcionou Mas pelo 2005 o row_count ficou sequencial para a tabela inteira

---

"Se esta sugestão for útil, por favor, classifique-a como útil.
Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta."

Érica Tohoma | http://bloggirlsa.wordpress.com

Answer 5

Não tenho o 2005 aqui para testar, mas acho que a função ROW_NUMBER deveria funcionar da mesma forma.

Para ser mais específico, o PARTITION BY F1.COD é quem deveria fazer com que o sequencial começasse do 1 novamente para cada COD.

Se o problema persistir, experimente desta outra forma:

DECLARE @TESTE TABLE 
(COD INT, CONCATENADO VARCHAR(50))

INSERT INTO @TESTE (COD, CONCATENADO) VALUES (13, '5+3+1.5+4+8.75')
INSERT INTO @TESTE (COD, CONCATENADO) VALUES (21, '12.1+3+2')
INSERT INTO @TESTE (COD, CONCATENADO) VALUES (55, '3')

;with CTE as
(
    SELECT 
        F1.COD,
        O.splitdata 
    FROM
    (
         SELECT 
             COD,
             cast('<X>' + replace(F.CONCATENADO, '+', '</X><X>') + '</X>' as XML) as xmlfilter 
         from @TESTE F
    ) F1
    CROSS APPLY
    ( 
         SELECT 
             fdata.D.value('.', 'varchar(50)') as splitdata 
         FROM f1.xmlfilter.nodes('X') as fdata(D)
    ) O
)

select 
    COD, 
    ROW_NUMBER() OVER(PARTITION BY COD ORDER BY COD) AS TESTE,
    splitdata
from CTE

Espero que ajude.

---

Assinatura: http://www.imoveisemexposicao.com.br

Answer 6

obrigada pela ajuda pessoal,

acho todas as soluções são válidas, principalmente aquelas que retornam as 3 colunas

mas o sistema onde eu tenho que realizar este script é o protheus, e como muitos sabem ele retorna registros em branco,

não sei pq quando executo as propostas de soluções, para as linhas que estão em branco, ele retona vários registros....

exemplo:

COD | TESTE | CONCATENADO

13 | 1 | *BRANCO

13 | 2 | *BRANCO

13 | 3 | *BRANCO

13 | 4 | *BRANCO

...

E assim por diante... a quantidade de linhas em branco não é igual para todos os cod em branco da tabela, ela varia...

pq será que ele faz isto?

será algo relacionado ao xml?

como eu poderia fazer para que ele retorne apenas 1 linha quando o "concatenar" estiver em branco?

obrigada

abs

---

"Se esta sugestão for útil, por favor, classifique-a como útil.
Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta."

Érica Tohoma | http://bloggirlsa.wordpress.com

Answer 7

Boa tarde,

DECLARE @TESTE TABLE 
(COD INT, CONCATENADO VARCHAR(50))

INSERT INTO @TESTE (COD, CONCATENADO) VALUES (13, '5+3+1.5+4+8.75')
INSERT INTO @TESTE (COD, CONCATENADO) VALUES (21, '12.1+3+2')
INSERT INTO @TESTE (COD, CONCATENADO) VALUES (55, '3')
INSERT INTO @TESTE (COD, CONCATENADO) VALUES (65, '')
INSERT INTO @TESTE (COD, CONCATENADO) VALUES (75, null)
INSERT INTO @TESTE (COD, CONCATENADO) VALUES (85, '      ')
INSERT INTO @TESTE (COD, CONCATENADO) VALUES (95, ' + + + ')
INSERT INTO @TESTE (COD, CONCATENADO) VALUES (96, '+++')

;with CTE as
(
    SELECT 
        F1.COD,
        O.splitdata 
    FROM
    (
         SELECT 
             COD,
             cast(CASE WHEN F.CONCATENADO IS NULL OR ltrim(replace(F.CONCATENADO, '+', '')) = ''
                      then '<X></X>' 
                      else '<X>' + replace(F.CONCATENADO, '+', '</X><X>') + '</X>'
                  end as XML) as xmlfilter 
         from @TESTE F
    ) F1
    CROSS APPLY
    ( 
         SELECT 
             fdata.D.value('.', 'varchar(50)') as splitdata 
         FROM f1.xmlfilter.nodes('X') as fdata(D)
    ) O
)

select 
    COD, 
    ROW_NUMBER() OVER(PARTITION BY COD ORDER BY COD) AS TESTE,
    splitdata
from CTE

Espero que seja útil.

---

Assinatura: http://www.imoveisemexposicao.com.br

Answer 8

Desculpem a demora, mas a junção de várias propostas de solução daqui me ajudaram a resolver meu problema!

Obrigada!

---

"Se esta sugestão for útil, por favor, classifique-a como útil.
Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta."

Érica Tohoma | http://bloggirlsa.wordpress.com