SQLITE - transposing rows into columns properly

Question

I've a database containing a table for products order like this:

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       ....

and a products prices table like this:

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       ....  

what I'd like to get, is a view like this (for simplicity filtered by 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

I already looked through the web and through SO, and I found THIS QUESTION that shows me exactly what I need to do, but I think with some modifications...

I tryed to run this query on the database:

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

will return in the prices columns, for every row, the MAX value found in the entire column: so both for [product_color = 001] and for [product_color = 995] I have price equals to 20 EURO (when for 001 it's cheaper, only 10 euro!)

I tryed also without MAX, but it gives me one row for each price, leaving lot of cells empty (then I understood what MAX is used for :D).

Do you know a way to do what I'm trying to do? How can I use MAX inside a single prod_color instead through every prod_color?

I hope you can understand what I wrote, thanks in advance, any help is appreciated.

(if you need something explained more clearly, just ask and asap I'll reply.

Thanks again, best regards

Answer 1

I added an extra JOIN condition: AND o.prod_color = p.prod_color

http://sqlfiddle.com/#!5/fadad/5

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
 AND o.prod_color = p.prod_color /* this line is added */

WHERE o.order_id = '1'
GROUP BY
  o.order_id,
  o.prod_code,
  o.prod_color,
  o.size,
  o.quantity

Simplified example queries without the GROUP BY shows the difference:

http://sqlfiddle.com/#!5/fadad/13