SQL Rearange data from multiple cells into one

Question

I am trying to create a view that groups by a particular column (let's say BRAND) and all instances of a 2nd column (let's say COLOR) for each BRAND, grouped in a single cell and separated by semicolon. It's hard for me to put it in words but this is what I am looking for:

FROM THIS TABLE

|BRAND  |COLOR  |
|-------|-------|
|TOYOTA |BLUE   |
|NISSAN |BLUE   |
|FORD   |BLUE   |
|NISSAN |RED    |
|NISSAN |GREEN  |
|FORD   |RED    |
-----------------

TO THIS VIEW

|BRAND  |COLOR          |
|-------|---------------|
|TOYOTA |BLUE           |
|NISSAN |BLUE;RED;GREEN |
|FORD   |BLUE;RED       |
|-------|---------------|

I wish I could provide code but I have not come up with any ideas.

Consider handling issues of data display in the presentation layer/application-level code, assuming you have that (e.g. a simple PHP loop acting upon an ordered array). — Strawberry, Jun 05 '20 at 19:14

zealous · Answer 1 · 2020-06-05T19:16:19.907

0

You can use group_concat. here is the demo.

select
  brand,
  group_concat(color order by color SEPARATOR ';' ) as color
from myTable
group by
  brand

output:

| brand  | color          |
| ------ | -------------- |
| FORD   | BLUE;RED       |
| NISSAN | BLUE;GREEN;RED |
| TOYOTA | BLUE           |

edited Jun 05 '20 at 19:16

answered Jun 05 '20 at 19:14

zealous

7,336
4
16
36

@Strawberry thanks for the feedback, I have updated my answer. – zealous Jun 05 '20 at 19:20
If it matters, this returns colors in a different order from that implied by the desired result ;-) – Strawberry Jun 05 '20 at 19:26
Thanks, I am on SSMS and there is no group_concat function in it. Is there an analogous option? – alexqr1 Jun 05 '20 at 19:27
@alexqr1 why did you tag `MySQL` then? – zealous Jun 05 '20 at 19:27
My mistake, I'm pretty new at this. – alexqr1 Jun 05 '20 at 19:33
@alexqr1 are you using ms sql server? – Shadow Jun 06 '20 at 07:46

SQL Rearange data from multiple cells into one

1 Answers1