How to concatenate multiple rows of strings into one row?

Question

I have two tables.

AAATRANSPORTLINE:

AAATRANSPORTTABLE   AAATOTYPECODEID Accessorial AAALINENUM
5637146144                M                        1.00
5637146144                                         1.60
5637146144               WNI                       3.00
5637146144               WNI                       16.00
5637146144               DIM                       18.00
5637146144               DIM                       20.00
5637146144               IVF           IVF         21.00
5637146144               NCM           NCM         23.00
5637146144               FSC                       24.00

AAATRANSPORTTABLE:

Pro Number  RECID
19035761    5637146144

I need this output:

Pro Number  RECID        Accessorial
19035761    5637146144    IVF,NCM

So i am trying to concatenate all string values in the Accessorial field of AAATRANSPORTLINE into one row of AAATRANSPORTTABLE.

I'm not sure how to concatentate the strings before I attempt to join the tables. I tried this query:

SELECT 
      t2.[AAATRANSPORTTABLE],
      t2.[AAATOTYPECODEID],
      coalesce(t2.[Accessorial] + ', ' + [Accessorial],[Accessorial]) as 'ACCESSORIAL',
      t2.[AAALINENUM]
    
FROM [AX2cProdStage].[dbo].[AX2cTestAdapter_dbo_AAATRANSPORTLINE_V] t2
where t2.[aaatransporttable] = '5637146144'

My results.

AAATRANSPORTTABLE   AAATOTYPECODEID ACCESSORIAL AAALINENUM
5637146144                 M             ,         1.00
5637146144                               ,         1.60
5637146144                WNI            ,         3.00
5637146144                WNI            ,         16.00
5637146144                DIM            ,         18.00
5637146144                DIM            ,         20.00
5637146144                IVF         IVF, IVF     21.00
5637146144                NCM         NCM, NCM     23.00
5637146144                FSC            ,         24.00

Don't you need column AAATOTYPECODEID and AAALINENUM in the final output? — Sujitmohanty30
– Sujitmohanty30, Commented Sep 10, 2020 at 20:00
@Sujitmohanty30, I only need to join ACCESSORIAL to AAATRANSPORTTABLE. I only left those two fields in the query for clarity. — user13365611
– user13365611, Commented Sep 10, 2020 at 20:04

Gordon Linoff · Accepted Answer · 2020-09-10 20:01:35Z

1

You can use for xml:

select tt.*,
       stuff( (select ',' + tl.Accessorial
               from AAATRANSPORTLINE tl
               where tt.RECID = tl.AAATRANSPORTTABLE and tl.Accessorial <> ''
               for xml path ('')
              ), 1, 1, ''
            ) as Accessorials
fro AAATRANSPORTTABLE tt

answered Sep 10, 2020 at 20:01

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to concatenate multiple rows of strings into one row?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related