1

I am having this sample table below

+---------+----------------------+
| PRODUCT | TYPE                 |
+---------+----------------------+
| WIN 10  | Home                 |
+---------+----------------------+
| WIN 10  | Pro                  |
+---------+----------------------+
| WIN 10  | Pro for Workstations |
+---------+----------------------+
| Linux   | Ubuntu               |
+---------+----------------------+
| Linux   | Red Hat              |
+---------+----------------------+
| Linux   | Fedora               |
+---------+----------------------+

I would like to change it (transpose) to have result like seen below using MySQL

+---------+-------------------------------+
| PRODUCT | TYPE                          |
+---------+-------------------------------+
| WIN 10  | Home,Pro,Pro for Workstations |
+---------+-------------------------------+
| Linux   | Ubuntu,Red Hat,Fedora         |
+---------+-------------------------------+

Could you please help me with that? I have tried everything I could using this forum and I am not able to get the result I need, thank you very much for your help

Brunoxy1
  • 13
  • 3

1 Answers1

0

In mysql You could use group_concat 

select product, group_concat(type)
from my_table 
group by product

or with order by 

select product, group_concat(type order by type)
from my_table 
group by product

for BigSQL the equivalent function should be LISTAGG(type)

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • for BigSQL you could try with LISTAGG(type) – ScaisEdge Jun 12 '20 at 11:05
  • I was finally able to concatenate the `TYPE` using `LISTAGG(DISTINCT TYPE,',') WITHIN GROUP (ORDER BY TYPE)` but I can only do that for one `PRODUCT` at a time when I specify it in `WHERE` clause. This siply does not work using `SELECT PRODUCT, LISTAGG(DISTINCT TYPE,',') WITHIN GROUP (ORDER BY TYPE) from My_table` – Brunoxy1 Jun 12 '20 at 12:51