SQL - Concat multiple rows into 1 on a join

Question

So I've got 2 tables which looks like the below:

MetaData

Id       | Value
---------------------
1       | 5
1       | 6
2       | 6

LookUp

Value    | Text
---------------------
5       | Car
6       | Truck

I need a query to pull out the Id from MetaData and the text from Lookup so e.g.

Id | Text
---------
1  | Car, Truck
2  | Truck

Any ideas on how I can do this? Thanks!

Answer 1

In SQL Server 2017, you would use string_agg():

select id, string_agg(l.test, ', ')
from metadata m join
     lookup l
     on m.value = l.value
group by m.id;

In earlier versions it is more complicated. You can Google "SQL Server string aggregation" and get lots of examples on what to do.