What’s a pivot?

Pivoting is the operation by which values in a column become column names, so that visually a kind of 90° rotation happens: what was read vertically top-to-bottom appears horizontally from left to right. This concept is also called “transposition”, or “crosstab”, and it brings a bit of the spreadsheet way of thinking into the relational way.

This is available in PostGreSQL, Oracle, SQL Server and others ....

I would love that feature and enhancement of what SQL is understood by SQite3.

My use case is as following: From several channels I get measurement values, each value in its own row with a set of identifying fields, which are repeating, like from a logfile. To further use the data by my customers they need them in single rows ... each channel one row with all the identifying fields and the values in a column each.

The number of columns to transpose is known, it is one of the repeating values given in each line. Also given is the sequence number. A typical sequence is running from 1 to 100 or 1 to 1000 or 1 to 2000.

Is there something foreseen to get this also done in SQLite3 ?

What would be the suggested way to achieve such a transposed result with SQLite3 ?

One solution could be the pivot fields as a CTE and then to join each column .... up to 2000 times. Would SQLite3 support such big SQL statements? Where are the limits in size?

I once saw in the forum a proposal for a table-valued extension function. Is this the only way for me to go?

If yes then I would at least love to see that to become part of the core in (one of the) next SQLite3 release(s).

I see many pro's and no contra for such a language enhancement in SQLite which already exists in other big players.

What are the answers of the developers?

By using the table-like behavior of pragma_table_xinfo, a Tcl script could be readily devised to create a transformation of a normal table into the debased form of data popularly known as the pivot table, and populate it.

There are several obstacles to this feature joining the SQLite panoply. One is that it works against the Lite aspect of SQLite. (This is not the weightless cost you seem to think it is.) Another is that a pivot table really is a debasement of the relational model for data. It would be much like other highly specialized, denormalized forms of output better relegated to those applications that need it. Finally, it would take development resources, now and in the years ahead, that would (probably) be better spent on better meeting the need for managing well-formed data.

Above is IMHO, of course.

jakethaw has a pivot_vtab extension that does that. I have a copy which you can get from http://www.dessus.com/files/pivot_vtab.c

There is also a statement_vtab.c http://www.dessus.com/files/statement_vtab.c that that lets you create a virtual table that runs an SQL statement (with parameters). ie, you can create virtual table test using statement_vtab((select a, b, c from t0 where b=:b)) so that you can "select * from test where b=42" and use it like a stored procedure/parameterized view.

I doubt there is presently any plan to add any such functionality to the SQLite3 core.

Note that pivot_vtab (https://github.com/jakethaw/pivot_vtab) has since been updated (over 12 months ago) with additional constructor validations, and enhanced to support multiple columns in the row query e.g.:

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