Concatenation of SQL 1 to many tables-CodePudding

My apologies if this has already been covered, I just could not find a suitable answer. If anyone know of one, please do let me know.

I wrote a quick tool to take all the fields in the second table and concatenate them, then update the AddtionalTripLineInfo field in the first table. it was fairly quick, however, my question is, is there an easier straight with a SQL query to do this and make the formatting the same?

I'm using SQL Server and it doesn't matter if this is not looking right or formatting, this is the job I was dealt and that is the format they want.

thank you in advance!

CodePudding user response：

From your image I'm going to guess you're using SQL Server, if so...

CONCAT() will combine the SeqNo and the Distance
STRING_AGG() will combine the rows

SELECT
  TripID,
  STRING_AGG(
    CONCAT(SeqNo, '|', ThruWayDistanceCalculated),
    '~'
  ) WITHIN GROUP (
      ORDER BY SeqNo
    )
FROM
  example
GROUP BY
  TripID

Demo : https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=c18456392ee5a242d45a945a8aaadbb2