Combine multiple instances of entry into one, differences into array

Question

I have a table filled with cars, another table that has the car IDs and the feature IDs and another table with the feature IDs and feature names.

A car can have multiple features in that table. The result of the query is, that i get multiple instances of the same car, each having a different feature.

I would like to have only one instance of the car (by ID) and combine all features into one column (list or array). I could do it in code after the query but i would like to do it in a query instead.

Cars table         Car property table      Property table
ID    Name         Car_ID  property_ID     property_id   Property_name
1     Audi         1       1               1             Aircon
2     BMW          1       2               2             Autopilot
3     Mercedes     2       1
                   3       2

Result is:

1 Audi Aircon
1 Audi Autopilot
2 BMW  Autopilot
3 Mercedes None

Result should be:

1 Audi [Aircon, Autopilot]
2 BMW   Autopilot
3 Mercedes None

Make it easy to assist you - show us some sample table data and the expected result (as formatted text, no images.) [mcve] — jarlh, Oct 30 '20 at 09:25
Does this answer your question? [How to concatenate text from multiple rows into a single text string in SQL server?](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) — CleanBold, Oct 30 '20 at 09:53

score 0 · Answer 1 · answered Oct 30 '20 at 11:07

0

This seems like a basic aggregation query with joins:

select c.name, group_concat(Property_name) as properties
from cars c left join
     car_properties cp
     on c.car_id = cp.car_id left join
     properties p
     on p.property_id = cp.property_id
group by c.name

answered Oct 30 '20 at 11:07

Gordon Linoff

1,242,037
58
646
786

Hi, thank you for your answer. Does "properties p" stand for the table property or a new col? – Barry Oct 30 '20 at 11:36
`properties` is the table name. `p` is a table alias. I generally pluralize table names, but obviously you should use the actual name in your database. – Gordon Linoff Oct 30 '20 at 12:24

Combine multiple instances of entry into one, differences into array

1 Answers1