April 7, 2008 at 4:38 pm
Hi, I have the follow script:
select x.guia
,(select atrb_cdg from atributos_suite ats where ats.clhl_cdg=g.clhl_cdg and atrb_cdg in ('CHRBK','FRDE','PFRDE')) atr_guia
from Guia
and I need convert the subselect in a string, by example "a,b,c,d". How I can do it this?
Thanks
April 7, 2008 at 7:00 pm
Lisset (4/7/2008)
Hi, I have the follow script:select x.guia
,(select atrb_cdg from atributos_suite ats where ats.clhl_cdg=g.clhl_cdg and atrb_cdg in ('CHRBK','FRDE','PFRDE')) atr_guia
from Guia
and I need convert the subselect in a string, by example "a,b,c,d". How I can do it this?
Thanks
Here's one way that should work.
select clhl_cdg, atrb_cdg,
row_number() over (partition by clhl_cdg order by atrb_cdg desc) as seq
into #ATS1
from atributos_suite
where atrb_cdg in ('CHRBK','FRDE','PFRDE');
with Appender( clhl_cdg, atrb_cdg, seq )
as ( select clhl_cdg, cast(atrb_cdg as varchar(max)), seq
from #ATS1 where seq = 1
union all
select A.clhl_cdg, A.atrb_cdg + ', '+ B.atrb_cdg, A.seq
from #ATS1 as A join Appender as B
on A.clhl_cdg = B.clhl_cdg and A.seq = B.seq + 1
)
select clhl_cdg, min(atrb_cdg) as atrb_cdg
into #ATS2
from Appender
group by clhl_cdg
-- since seq is based on atrb_cdg DESC, min() will have all concatenated values
-- join to #ATS2 via clhl_cdg
select G.clhl_cdg, X.atrb_cdg as atr_guia
from Guia as G join #ATS2 as X
on G.clhl_cdg = X.clhl_cdg
April 7, 2008 at 8:48 pm
I'm pretty sure recursion will be a performance killer here... please check out the XML code at the following URL (last bluish/purple code box in the article)...
http://qa.sqlservercentral.com/articles/Test+Data/61572/
--Jeff Moden
April 8, 2008 at 6:05 am
that's a really good hack Jeff. by omitting an alias on the column and using path(''), the result isn't even XML but it's darn useful! and much more brief.
select g.guia, stuff( (select ', '+ atrb_cdg
from atributos_suite ats
where ats.clhl_cdg=g.clhl_cdg and atrb_cdg in ('CHRBK','FRDE','PFRDE')
for xml path('')), 1, 1, '' ) atr_guia
from Guia as g
April 8, 2008 at 11:24 am
Thanks Antonio, thats just what I needed.
April 8, 2008 at 12:39 pm
don't thank me... thank jeff m. he's the one that did it. 😀
i just filled in the blanks for your specific statement.
April 8, 2008 at 12:55 pm
Jeff, thanks by your help. Thats just what I needed.
April 8, 2008 at 6:00 pm
Thanks for the feedback folks. 🙂
--Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply