Unify multiple SQL rows

Question

For each ids, several names appear in several rows. How can I make all of them appear in a single row?

select 
      cli.client, stb.macaddres, stb.unica as card, 
      pro.name 
from 
      clientes cli
inner join boxes stb on stb.nuclient = cli.nuclient
inner join date.servicio ser on ser.ids = stb.ids
inner join date.producto pro on pro.proid = ser.serid
where 
      cli.client=7;

Unexpected Results :

client macaddress  única  name
7       xxxxxxxxx  56565 product1
7       xxxxxxxxx  56565 product2
7       xxxxxxxxx  56565 product3

Desired Results :

client  macaddress  única  name
 7       xxxxxxxxx  56565 product1, product2,product3`

Why? Who desires the results in that format? It can be done, but often that format is a sign of bad requirements or poor design. — , Oct 26 '18 at 00:05

score 0 · Accepted Answer · answered Oct 26 '18 at 01:40

0

You can do listagg() as gordon said in comment you can read here about documentation ListAGG()

And for your query :

select 
     cli.client, stb.macaddres, stb.unica as card 
     LISTAGG(pro.name, ', ') WITHIN GROUP (ORDER BY pro.proid ASC) "Name"
from 
     clientes cli
inner join boxes stb on stb.nuclient = cli.nuclient
inner join date.servicio ser on ser.ids = stb.ids
inner join date.producto pro on pro.proid = ser.serid
where 
     cli.client = 7;
Group By
    cli.client, stb.macaddres, stb.unica

answered Oct 26 '18 at 01:40

dwir182

1,539
10
20

Thanks for your answer is exactly what I wanted – Javier Malag Oct 26 '18 at 14:01
Gladly to help.. :) – dwir182 Oct 26 '18 at 14:02

Unify multiple SQL rows

1 Answers1