Combine multiple similar columns into single column

Question

I have a table like

Id    RefNumber     LotNum
---------------------------
1      Ref-1        10
2      Ref-1        11

Lotnumber:

Lot-Id    Lot-Name
-------------------
10         Apple
11        Banana

I need my output to look like this:

Ref-1    Apple,Banana

Please help me - how can I achieve this?

it does help https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv — Prasanna Kumar J, Jun 08 '20 at 08:52
What is your sql server version, and what compatibility version ? — Caius Jard, Jun 08 '20 at 08:52
Note that prasanna has linked to a question that has many hacks for what should now be done by group_concat if it is available.. — Caius Jard, Jun 08 '20 at 08:54

score 1 · Answer 1 · answered Jun 08 '20 at 08:52

1

On SQL Server 2017 and later, we can use STRING_AGG here:

SELECT
    r.RefNumber,
    STRING_AGG(l.[Lot-Name]) WITHIN GROUP (ORDER BY l.[Lot-Id]) AS LotNames
FROM Refs r
LEFT JOIN Lotnumber l
    ON r.LotNum = l.[Lot-Id]
GROUP BY
    r.RefNumber;

answered Jun 08 '20 at 08:52

Tim Biegeleisen

502,043
27
286
360

Combine multiple similar columns into single column

1 Answers1