SQL JOIN or GROUP BY variant?

Question

I have a table named "articulos_precios" like this:

 articulo_id  precio  precio_empresa_id
 ----------- -------- -----------------
      1         10          11
      2         15          12
      1         20          13
      3         45          13
      2         10          13
      3         40          11
      1         25          12
      2         10          11
      3         40          12

I need obtain something like this:

 articulo_id  precio1  precio2   precio3
 ----------- -------- --------- ---------
      1         10       25         20
      2         10       15         10     
      3         40       40         45

where:

precio1 is the price for precio_empresa_id:11,

precio2 is the price for precio_empresa_id:12

precio3 is the price for precio_empresa_id:13

I have try with this query:

select M.articulo_id,M.precio1,M.precio2,U.precio3 from
(select M.articulo_id,M.precio1,M.precio2 from
  (select Z.articulo_id,Z.precio as precio1 from precios_articulos Z where Z.precio_empresa_id=42) W full join
  (select Y.articulo_id,Y.precio as precio2 from precios_articulos Y where Y.precio_empresa_id=43) V
on W.articulo_id=V.articulo_id) M full join
  (select X.articulo_id,X.precio as precio3 from precios_articulos X where X.precio_empresa_id=42) U
on M.articulo_id=U.articulo_id;

but it have bery low performance. Its possible to do it with some variant of Group By?? or somebody know how to do it?

This question has ben marqued as duplicate respect to: MySQL pivot table, The main difference is that in this example the occurrences are not counted, but rather the contents of the table are grouped and restructured. Regards @Barmar

this is called a pivot. mySQL doesn't support pivot but it can be emulated using case and max. — xQbert, Jun 01 '17 at 15:07
Seriously consider handling issues of data display in application level code. — Strawberry, Jun 01 '17 at 15:08

xQbert · Accepted Answer · 2017-06-01T15:48:18.783

Presentation should be handled at the presentation layer. However, if you must.... you can accomplish it this way:

SELECT articulo_id
     , max(case when precio_empresa_ID = 11 then precio end) as Precio1
     , max(case when precio_empresa_ID = 12 then precio end) as Precio2
     , max(case when precio_empresa_ID = 13 then precio end) as Precio3
FROM articulos_precios
GROUP BY articulo_id

Note: if you need additional precio_empresa_ID you have to add additional case and max statements.

This can be accomplished using dynamic SQL if needed;but this gives the provides the general construct.

Alternatively instead of using max you could use a sum or count if your data has multiple occurrences and you need to sum / counting them instead of just displaying values.

SELECT articulo_id
     , sum(case when precio_empresa_ID = 11 then coalesce(precio,0) end) as Precio1
     , sum(case when precio_empresa_ID = 12 then coalesce(precio,0)end) as Precio2
     , sum(case when precio_empresa_ID = 13 then coalesce(precio,0)end) as Precio3
FROM articulos_precios
GROUP BY articulo_id

Wow!, its amazing, just i need. Tankyou Very Much!!. – uomo_perfetto Jun 01 '17 at 15:41 — uomo_perfetto, Jun 01 '17 at 15:41

SQL JOIN or GROUP BY variant?

1 Answers1