Concatenate 2 rows based on grouping

  • ChrisM@Work (6/20/2012)


    pwalter83 (6/20/2012)


    ChrisM@Work (6/20/2012)


    Are you seeing one row per BOOKING_ID in your output?

    - here's where adequate sample data would have helped πŸ™‚

    yeah, I get only one row per BOOKING_ID and that is what I want. somehow it does'nt work for GEN_COMMOD_CD column. would I have to tweak the FOR XML PATH syntax ?

    Per your solution, I was wondering if the 2 values were identical, would it still show up as 2 different values separated by comma or would it just show a single value (representing both) ?

    I asked because in the GEN_COMMOD_CD column the values are identical (Loudspeakers), so I thought if it would show up as Loudspeakers instead of - Loudspeakers, Loudspeakers

    Are you absolutely sure there are two rows in table GEN_COMMOD_CD for the booking ID you're testing with? Check by doing a select against GEN_COMMOD_CD alone.

    Sorry for wasting your time. You are right, there is only a single entry for this column. I need to wake up.

    Thanks for your immense help. I think its done now.

  • ChrisM@Work (6/20/2012)


    pwalter83 (6/20/2012)


    ChrisM@Work (6/20/2012)


    Are you seeing one row per BOOKING_ID in your output?

    - here's where adequate sample data would have helped πŸ™‚

    yeah, I get only one row per BOOKING_ID and that is what I want. somehow it does'nt work for GEN_COMMOD_CD column. would I have to tweak the FOR XML PATH syntax ?

    Per your solution, I was wondering if the 2 values were identical, would it still show up as 2 different values separated by comma or would it just show a single value (representing both) ?

    I asked because in the GEN_COMMOD_CD column the values are identical (Loudspeakers), so I thought if it would show up as Loudspeakers instead of - Loudspeakers, Loudspeakers

    Are you absolutely sure there are two rows in table GEN_COMMOD_CD for the booking ID you're testing with? Check by doing a select against GEN_COMMOD_CD alone.

    Just one thing Chris, what would I have to do if I need to display a column from the table that has been used to display concatenated values ?

    For e.g. I need to display another column REEFER_FLG without any concatenation that is in the GENERAL_COMMODITY_CD table

    -----------------------------------------------------------------------------------

    CROSS APPLY (

    SELECT

      =

      STUFF(

      (SELECT ', ' + mbc.GENERAL_COMMODITY_CD

      FROM MG_BOOKING_COMMODITY mbc

      WHERE mbc.BOOKING_ID = mb.BOOKING_ID

      --AND mber.BOOKING_NUM = mb.BOOKING_NUM AND mber.TRADE_CD = mb.TRADE_CD

      --ORDER BY GENERAL_COMMODITY_CD

      FOR XML PATH(''), TYPE).value('.', 'varchar(max)')

      ,1,2,'')

      ) b (GEN_COMMOD_CD)

    1. Since GENERAL_COMMODITY_CD table has a different cardinality to your result set, having multiple rows per BOOKING_ID, you will need to preaggregate it, either as a CROSS APPLY or as a derived table. Have a go, if you get stuck, repost.

      β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

      For fast, accurate and documented assistance in answering your questions, please read this article.
      Understanding and using APPLY, (I) and (II) Paul White
      Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

    2. ChrisM@Work (6/20/2012)


      Since GENERAL_COMMODITY_CD table has a different cardinality to your result set, having multiple rows per BOOKING_ID, you will need to preaggregate it, either as a CROSS APPLY or as a derived table. Have a go, if you get stuck, repost.

      I used the Cross apply technique as you mentioned and it worked. Thanks again !

    Viewing 4 posts - 31 through 33 (of 33 total)

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