Post reply

SUBSELECT to string

  • April 7, 2008 at 4:38 pm

    #379694

    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

    #799495

    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

    #799519

    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

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • April 8, 2008 at 6:05 am

    #799662

    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

    #799916

    Thanks Antonio, thats just what I needed.

  • April 8, 2008 at 12:39 pm

    #799959

    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

    #799965

    Jeff, thanks by your help. Thats just what I needed.

  • April 8, 2008 at 6:00 pm

    #800071

    Thanks for the feedback folks. 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply