Tengo una mesa (product_tb):

  id   product   unit   quantity
 ---- --------- ------ ----------
   1   A         1kg         100
   2   B         1kg          50
   3   A         5kg          50

Quiero salida como:

  product   1kg   2kg   5kg
 --------- ----- ----- -----
  A         100     0    50
  B          50     0     0

Gracias de antemano

0
saujan rajbhandari 15 feb. 2018 a las 10:19

2 respuestas

La mejor respuesta

Sí, con CASE WHEN y agregaciones. Prueba esto:

SELECT product, SUM(CASE WHEN unit='1kg' THEN quantity ELSE 0 END) AS '1kg', 
                SUM(CASE WHEN unit='2kg' THEN quantity ELSE 0 END) AS '2kg',
                SUM(CASE WHEN unit='5kg' THEN quantity ELSE 0 END) AS '5kg'
FROM YourTable
GROUP BY product, unit; 

Para sumar el total hacer:

SELECT product, SUM(CASE WHEN unit='1kg' THEN quantity ELSE 0 END) AS '1kg', 
                SUM(CASE WHEN unit='2kg' THEN quantity ELSE 0 END) AS '2kg',
                SUM(CASE WHEN unit='5kg' THEN quantity ELSE 0 END) AS '5kg',
                SUM(quantity) AS Total
FROM YourTable
GROUP BY product, unit; 
0
cdaiga 15 feb. 2018 a las 09:38

La mayoría del operador de soporte de DBMS PIVOT

select * from
( 
   select Product, Unit, quantity from table
) t
pivot
(
    max(quantity) for Unit in([1kg], [2kg] ,[5kg])
)a  
0
Yogesh Sharma 15 feb. 2018 a las 07:29