SQLITE - transpondo linhas em colunas corretamente
Eu tenho um banco de dados contendo uma tabela para produtos como este:
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 ....
e uma tabela de preços de produtos 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 ....
o que eu gostaria de obter, é uma visão como esta (por simplicidade 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
Eu já olhei pela web e pelo SO, e encontreiESSA QUESTÃO isso me mostra exatamente o que eu preciso fazer, mas acho que com algumas modificações ...
Eu tentei executar essa consulta no banco de dados:
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
retornará nas colunas de preços, para cada linha, o valor MAX encontrado em toda a coluna: portanto, tanto para [product_color = 001] quanto para [product_color = 995], o preço é igual a 20 euros (quando for 001, é mais barato 10 euro!)
Eu tentei também sem MAX, mas isso me deu uma linha para cada preço, deixando muitas células vazias (então eu entendi o que o MAX é usado para: D).
Você sabe como fazer o que eu estou tentando fazer? Como posso usar o MAX dentro de um único prod_color ao invés de cada prod_color?
Espero que você possa entender o que eu escrevi, obrigado antecipadamente, qualquer ajuda é apreciada.
(se você precisar de algo explicado com mais clareza, basta perguntar e o mais rápido possível eu responderei.
Mais uma vez obrigado, cumprimentos