[RFE] Allow multiple columns in row pivot query

[saaj]

Currently the vtable interprets only 1st column in the result set of the 1st pivot query. There are cases where pivot is grouped by multiple columns which are not pivoted. See this Stack Overflow answer for an example.

As a workaround a temporary row-map table can be created and later pivot table can be joined with it to reconstruct the columns.

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

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

[jakethaw]

This support has now been added. Documentation will be updated in the coming days. In the meantime, here is a usage example based on the Stack Overflow answer:

.load ./pivot_vtab
.headers on
.mode column

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');

CREATE VIRTUAL TABLE pivot USING pivot_vtab(
  (SELECT * FROM products_order),
  (SELECT currency, currency FROM products_prices GROUP BY currency),   
  (
    SELECT price
      FROM products_order o
      JOIN products_prices p USING(prod_code, prod_color)
     WHERE o.order_id = :order_id
       AND o.prod_code = :prod_code
       AND o.prod_color = :prod_color
       AND o.size = :size
       AND p.currency = :currency
  )
);

SELECT * FROM pivot;

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

[saaj]

Nice, thank you for implementing this enhancement. I've added a comment to the SO answer that the temporary table is not needed any more.

Documentation will be updated in the coming days.

It would be helpful to add the minimal version of libsqlite3-dev that's required by pivot_vtab there. I guess it's 3.24 that introduced new string API. For instance, on 3.11 (Ubuntu Xenial) I have Error: ./pivot_vtab.so: undefined symbol: sqlite3_str_finish.