1

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
Improve this question
4
  • What version of SQL Server? Commented Sep 10, 2020 at 19:54
  • I'm using SQL Server 2016 Commented Sep 10, 2020 at 19:56
  • Don't you need column AAATOTYPECODEID and AAALINENUM in the final output? 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. Commented Sep 10, 2020 at 20:04

1 Answer 1

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments