Merge record values into a record that contains multiple values with comma separated

Question

I have a many-to-many table schema like this,

|------------|          |--------------|            |-----------|
| Products   |-|-------<|ProductEngine |>---------|-|  Engines  |
|            |          |              |            |           |
|------------|          |--------------|            |-----------|

After inner join query, I've got a result set below:

SELECT product_id, engine_id 
FROM Products 
INNER JOIN ProductEngine 
  ON Products.product_id=ProductEngine.product_id

+---------------+--------------+
| product_id    | engine_id    |
+---------------+--------------+
| P001          | E001         |
| P001          | E002         |
| P002          | E003         |
| P002          | E004         |
| P002          | E005         |
+---------------+--------------+

How do I write SQL query if I want to get a result like this?

+---------------+----------------+
| product_id    | engine_id      |
+---------------+----------------+
| P001          | E001,E002      |
| P002          | E003,E004,E005 |
+---------------+----------------+

possible duplicate of [Concatenate values based on ID](http://stackoverflow.com/questions/6603319/concatenate-values-based-on-id) — Mikael Eriksson, Dec 13 '11 at 09:18
and of http://stackoverflow.com/q/7755815/27535 and 100s of others — gbn, Dec 13 '11 at 09:23

score 2 · Accepted Answer · edited Jan 18 '21 at 12:30

2

They are several techniques to converting row values into a string, I like this one:

SELECT p1.product_id,
       substring(
          ( SELECT ',' + engine_id
              FROM ProductEngine p2
              WHERE p2.product_id = p1.product_id
              FOR XML PATH('') ), 2, 4000)
       AS engine_id
      FROM Products p1;

Edited

Now is working as you need. I have write an example to you.

edited Jan 18 '21 at 12:30

Community

1
1

answered Dec 13 '11 at 09:20

dani herrera

48,760
8
117
177

There is an error "Column 'Products.product_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." – sovantha Dec 13 '11 at 09:40
When I removed the GROUP BY clause, it works but there's a ',' left in the engine_id column.(E001,E002,)(E003,E004,E005,) – sovantha Dec 13 '11 at 09:42

Merge record values into a record that contains multiple values with comma separated

1 Answers1