CODEWITHSUNDEEP
sqlsql-server
user13365611
user13365611

Reputation:

How to concatenate multiple rows of strings into one row?

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

Upvotes: 1

Views: 91

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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

Upvotes: 1

Related Questions