Sqlite - Transposing Rows Into Columns Properly

I've a database containing a table for products order like this: order_id | prod_code | prod_color | size | quantity | ----------------------------------------------------- 1

Solution 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(CASEWHEN p.currency ='EUR'THEN p.price END) AS'EUR',
  MAX(CASEWHEN p.currency ='USD'THEN p.price END) AS'USD',
  MAX(CASEWHEN p.currency ='YEN'THEN p.price END) AS'YEN',
  MAX(CASEWHEN 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'GROUPBY
  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

Solution 2:

The dynamic query approach (with CASE expression per pivot column) should be preferred one, but when you don't know the pivot columns upfront or it's hard to generate the querty on the application side for some reason, there's an alternative in the form of pivot_vtab SQLite virtual table extension. It's less practical at the time of writing because it seems a proof-of-concept work, but it doesn't mean it can/will not improve.

I won't go into details of building the extension -- you can see them in this related answer. I'm using the data from the question like this.

CREATETABLE products_order(
  order_id INTEGER,
  prod_code TEXT,
  prod_color INTEGER,
  size TEXT,
  quantity INTEGER
);

INSERTINTO products_order(order_id, prod_code, prod_color, size, quantity)
VALUES
  (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);

CREATETABLE products_prices(
  prod_code TEXT,
  prod_color INTEGER,
  price INTEGER,
  currency TEXT
);

INSERTINTO products_prices(prod_code, prod_color, price, currency)
VALUES
  ('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');

The pivot is built like this (and I assume the extension is loaded, .load ./pivot_vtab).

1. Because the extension is limited to only one "row" column (on which you group-by), create a temporary mapping. I assume the order key is (order_id, prod_code, prod_color, size) and quantity functionally depends on order_id, so there's no need to group-by on it, but it'll be used on step 3.

   CREATETABLE rowmap ASSELECTROW_NUMBER() OVER(ORDERBY1) row_id, 
     order_id,  
     prod_code,  
     prod_color,  
     size, 
     quantity
   FROM products_order
   GROUPBY2, 3, 4, 5;
   

2. Create pivot table using the rowmap table. Note that the 3rd pivot "matrix filling" query also expects only single column.

   CREATE VIRTUAL TABLE pivot USING pivot_vtab(
     (SELECT row_id FROM rowmap),
     (SELECT currency, currency FROM products_prices GROUPBY currency),   
     (
       SELECT price
       FROM products_order o
       JOIN products_prices p USING(prod_code, prod_color)
       JOIN rowmap m USING(order_id, prod_code, prod_color, size)
       WHERE o.order_id =1AND m.row_id = ?1AND p.currency = ?2GROUPBY o.order_id, o.prod_code, o.prod_color, o.size, p.currency
     )
   );
   

3. Re-combine rowmap columns.

   SELECT order_id, prod_code, prod_color, size, quantity, p.*
   FROM pivot p
   JOIN rowmap r ON p.row_id = r.row_id;
   

   The result set should look like this (I have .headers on and .mode column).

   order_id    prod_code   prod_color  size        quantity    row_id      EUR         RUB         USD         YEN       
   ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------
   1           SHIRT       1           L           8           1           10          15          9           50        
   1           SHIRT       1           M           7           2           10          15          9           50        
   1           SHIRT       1           S           10          3           10          15          9           50        
   1           SHIRT       1           XL          1           4           10          15          9           50        
   1           SHIRT       995         L           0           5           20          45          29          100       
   1           SHIRT       995         M           1           6           20          45          29          100       
   1           SHIRT       995         S           2           7           20          45          29          100       
   1           SHIRT       995         XL          1           8           20          45          29          100  
   

4. Optionally, if you want get rid of the row_id column, you can drop it using another temporary table (ALTER TABLE ... DROP COLUMN is supported since SQLite 3.35).

   CREATETABLE result_pivot ASSELECT order_id, prod_code, prod_color, size, quantity, p.*FROM pivot p
   JOIN rowmap r ON p.row_id = r.row_id;
   
   ALTERTABLE result_pivot DROPCOLUMN row_id;
   
   SELECT*FROM result_pivot;