SQLITE - transposición de filas en columnas correctamente
Tengo una base de datos que contiene una tabla para productos ordenados así:
order_id | prod_code | prod_color | size | quantity |
-----------------------------------------------------
1 SHIRT 001 S 10
1 SHIRT 001 M 7
1 SHIRT 001 L 8
1 SHIRT 001 XL 1
1 SHIRT 995 S 2
1 SHIRT 995 M 1
1 SHIRT 995 L 0
1 SHIRT 995 XL 1
2 PANTS ....
y una tabla de precios de productos como esta:
prod_code | prod_color | price | currency
-----------------------------------------
SHIRT 001 10 EUR
SHIRT 001 9 USD
SHIRT 001 50 YEN
SHIRT 001 15 RUB
SHIRT 995 20 EUR
SHIRT 995 29 USD
SHIRT 995 100 YEN
SHIRT 995 45 RUB
PANTS ....
Lo que me gustaría obtener, es una vista como esta (por simplicidad filtrada por order_id):
order_id | prod_code | prod_color | size | quantity | EUR | USD | YEN | RUB
---------------------------------------------------------------------------
1 SHIRT 001 S 10 10 9 50 15
1 SHIRT 001 M 7 10 9 50 15
1 SHIRT 001 L 8 10 9 50 15
1 SHIRT 001 XL 1 10 9 50 15
1 SHIRT 995 S 2 20 29 100 45
1 SHIRT 995 M 1 20 29 100 45
1 SHIRT 995 L 0 20 29 100 45
1 SHIRT 995 XL 1 20 29 100 45
Ya busqué en la web y en SO, y encontréESTA PREGUNTA eso me muestra exactamente lo que necesito hacer, pero creo que con algunas modificaciones ...
Intenté ejecutar esta consulta en la base de datos:
SELECT o.order_id, o.prod_code, o.prod_color, o.size, o.quantity,
MAX(CASE WHEN p.currency = 'EUR' THEN p.price END) AS 'EUR',
MAX(CASE WHEN p.currency = 'USD' THEN p.price END) AS 'USD',
MAX(CASE WHEN p.currency = 'YEN' THEN p.price END) AS 'YEN',
MAX(CASE WHEN p.currency = 'RUB' THEN p.price END) AS 'RUB'
FROM products_order o JOIN products_prices p ON o.prod_code = p.prod_code
WHERE o.order_id = 1
GROUP BY o.order_id, o.prod_code, o.prod_color, o.size, o.quantity
volverá en las columnas de precios, para cada fila, el valor MAX encontrado en toda la columna: así que tanto para [product_color = 001] como para [product_color = 995] tengo un precio igual a 20 EURO (cuando para 001 es más barato, solo 10 euros!)
También probé sin MAX, pero me da una fila por cada precio, dejando muchas celdas vacías (entonces entendí para qué se utiliza MAX: D).
¿Conoces una forma de hacer lo que estoy tratando de hacer? ¿Cómo puedo usar MAX dentro de un solo prod_color en lugar de cada prod_color?
Espero que puedan entender lo que escribí, gracias de antemano, cualquier ayuda es apreciada.
(Si necesita que le expliquen algo más claramente, solo pregunte y lo antes posible le responderé).
Gracias de nuevo, un saludo