Hi Shane, You might be interested in trying out my pivot virtual table implementation.
https://github.com/jakethaw/pivot_vtab This will cater for changing values, but like a VIEW implementation, it does not satisfy your criteria of dynamically changing rows/columns. Changes to rows/columns can only be propagated by dropping and re-creating the virtual table. I have provided an example below using your data structure, however it works nicer with normalized data. e.g. .load ./pivot_vtab .headers on .mode column CREATE TABLE t( "Product/Region", Belgium, France, USA ); INSERT INTO t VALUES ('Oil_filter', 1, 2, 3), ('Spark_plug', 4, 5, 6), ('Coolent', 7, 8, 9); CREATE VIRTUAL TABLE pivot USING pivot_vtab( (SELECT name "Product/Region" -- pivot table key FROM pragma_table_info('t') WHERE name <> 'Product/Region'), (SELECT "Product/Region", -- pivot column key - can be referenced in pivot query as ?2, "Product/Region" -- pivot column name FROM t), (SELECT CASE ?1 WHEN 'Belgium' THEN Belgium WHEN 'France' THEN France WHEN 'USA' THEN USA END FROM t WHERE "Product/Region" = ?2) ); SELECT * FROM pivot; Product/Region Oil_filter Spark_plug Coolent -------------- ---------- ---------- ---------- Belgium 1 4 7 France 2 5 8 USA 3 6 9 -Jake On Mon, Apr 1, 2019 at 7:07 AM Shane Dev <devshan...@gmail.com> wrote: > > Hello, > > Is it possible to create a view which switches rows and columns of a > dynamically changing table? > > For example, imagine we have table t1 where both columns and rows could > change after the view has been created > > sqlite> select * from t1; > Product/Region|Belgium|France|USA > Oil_filter|1|2|3 > Spark_plug|4|5|6 > Coolent|7|8|9 > > Could view v1 be created such that > > sqlite> select * from v1; > Product/Region|Oil_filter|Spark_plug|Coolent > Belgium|1|4|7 > France|2|5|8 > USA|3|6|9 > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
