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

SQLFiddle: http://sqlfiddle.com/#!5/fadad – biziclop Aug 03 '12 at 17:24 — biziclop, Aug 03 '12 at 17:24

score 14 · Accepted Answer · answered Aug 03 '12 at 17:31

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

saaj · Answer 2 · 2021-07-02T20:20:40.173

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.

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

INSERT INTO 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);

CREATE TABLE products_prices(
  prod_code TEXT,
  prod_color INTEGER,
  price INTEGER,
  currency TEXT
);

INSERT INTO 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).

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.
```
CREATE TABLE rowmap AS 
SELECT 
  ROW_NUMBER() OVER(ORDER BY 1) row_id, 
  order_id,  
  prod_code,  
  prod_color,  
  size, 
  quantity
FROM products_order
GROUP BY 2, 3, 4, 5;
```

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 GROUP BY 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 = 1 AND m.row_id = ?1 AND p.currency = ?2
    GROUP BY o.order_id, o.prod_code, o.prod_color, o.size, p.currency
  )
);

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

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

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

ALTER TABLE result_pivot DROP COLUMN row_id;

SELECT * FROM result_pivot;

The author of `pivot_vtab` has implemented [this enchantment](https://github.com/jakethaw/pivot_vtab/issues/4), so the temporary table is not needed any more. — saaj, Jun 14 '21 at 20:17

SQLITE - transposing rows into columns properly

2 Answers2

Linked