Re: [sqlite] Is it possible to transpose a table using SQL?
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 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
Re: [sqlite] Is it possible to transpose a table using SQL?
On 1 Apr 2019, at 10:18am, Dominique Devienne wrote: > I can't seem to find that one, but I found another here: > https://metacpan.org/pod/SQLite::VirtualTable::Pivot I looked at that one, but it doesn't do what OP wants, which is to swap rows and columns without the programmer having to specify anything. It would be possible to implement the transform pivot as a virtual table in any language. But you do have to do the work: the feature doesn't come with SQLite. And if you're writing code you might as well do it in your program. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to transpose a table using SQL?
On Mon, Apr 1, 2019 at 7:15 AM Shane Dev wrote: > [...]. By "dynamically changing table", I meant the number of columns and > rows could could change > after the dependant view was created. it appears this is impossible using > only SQL > It's possible using a virtual table, which years ago a colleague used in our app. I can't seem to find that one, but I found another here: https://metacpan.org/pod/SQLite::VirtualTable::Pivot I don't see anything related to pivot/transpose in either usual locations below: https://www.sqlite.org/src/dir?ci=6cf8b18ec20f11c2&name=ext/misc https://www.sqlite.org/contrib ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to transpose a table using SQL?
Hi Keith, Jean-Luc I should have mentioned my shell is configured to display column headers Product/Region|Belgium|France|USA for table t1 and Product/Region|Oil_filter|Spark_plug|Coolent for view v1. By "dynamically changing table", I meant the number of columns and rows could could change after the dependant view was created. it appears this is impossible using only SQL On Mon, 1 Apr 2019 at 02:38, Keith Medcalf wrote: > > On Sunday, 31 March, 2019 14:07, Shane Dev wrote: > > >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 > > You example is ambiguous. > > For example, is the table T1 thus: > > create table T1("Product/Region" text not null, Belgium integer not null, > France integer not null, USA integer not null); > insert into T1 values ('Oil_filter', 1, 2, 3); > insert into T1 values ('Spark_plug', 4, 5, 6); > insert into T2 values ('Coolent', 7, 8, 9); > > or so: > > create table T1(c0, c1, c2, c3); > insert into T1 values ('Product/Region', 'Belgium', 'France', 'USA'); > insert into T1 values ('Oil_filter', 1, 2, 3); > insert into T1 values ('Spark_plug', 4, 5, 6); > insert into T2 values ('Coolent', 7, 8, 9); > > If "so", how do you know which column/row is the proposed row/column > names? Or do you just want to transpose the matrix? > > Please explain what you mean by "dynamically changing table" ... what > exactly is dynamically changing? The number of columns? The number of > rows? > > Note this is probably relatively simple for kiddie sized data but would be > far more efficient if you did it at the application level. It would be > even simpler if the data were properly normalized. > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > > > > ___ > 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
Re: [sqlite] Is it possible to transpose a table using SQL?
On Sunday, 31 March, 2019 14:07, Shane Dev wrote: >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 You example is ambiguous. For example, is the table T1 thus: create table T1("Product/Region" text not null, Belgium integer not null, France integer not null, USA integer not null); insert into T1 values ('Oil_filter', 1, 2, 3); insert into T1 values ('Spark_plug', 4, 5, 6); insert into T2 values ('Coolent', 7, 8, 9); or so: create table T1(c0, c1, c2, c3); insert into T1 values ('Product/Region', 'Belgium', 'France', 'USA'); insert into T1 values ('Oil_filter', 1, 2, 3); insert into T1 values ('Spark_plug', 4, 5, 6); insert into T2 values ('Coolent', 7, 8, 9); If "so", how do you know which column/row is the proposed row/column names? Or do you just want to transpose the matrix? Please explain what you mean by "dynamically changing table" ... what exactly is dynamically changing? The number of columns? The number of rows? Note this is probably relatively simple for kiddie sized data but would be far more efficient if you did it at the application level. It would be even simpler if the data were properly normalized. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to transpose a table using SQL?
If by "a dynamically changing table " you mean that any data change (not schema change) in t1 will propagate to v1, and if the set of Product values doesn't change, then you can try this: create table t1(Product,Belgium,France,USA); insert into t1 values ('OilFilter',1,2,3),('SparkPlug',4,5,6),('Coolent',7,8,9); select * from t1; create view v1(Region,OilFilter,SparkPlug,Coolent) as select 'Belgium' as Region, sum(case Product when 'OilFilter' then Belgium else null end) as OilFilter, sum(case Product when 'SparkPlug' then Belgium else null end) as SparkPlug, sum(case Product when 'Coolent' then Belgium else null end) as Coolent from t1 union select 'France' as Region, sum(case Product when 'OilFilter' then France else null end) as OilFilter, sum(case Product when 'SparkPlug' then France else null end) as SparkPlug, sum(case Product when 'Coolent' then France else null end) as Coolent from t1 union select 'USA' as Region, sum(case Product when 'OilFilter' then USA else null end) as OilFilter, sum(case Product when 'SparkPlug' then USA else null end) as SparkPlug, sum(case Product when 'Coolent' then USA else null end) as Coolent from t1; select * from v1; t1: +---+-++-+ | Product | Belgium | France | USA | +---+-++-+ | OilFilter | 1 | 2 | 3 | | SparkPlug | 4 | 5 | 6 | | Coolent | 7 | 8 | 9 | +---+-++-+ v1: +-+---+---+-+ | Region | OilFilter | SparkPlug | Coolent | +-+---+---+-+ | Belgium | 1 | 4 | 7 | | France | 2 | 5 | 8 | | USA | 3 | 6 | 9 | +-+---+---+-+ J-L Hainaut On 31/03/2019 22:07, Shane Dev 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
Re: [sqlite] Is it possible to transpose a table using SQL?
On 31 Mar 2019, at 9:07pm, Shane Dev wrote: > Is it possible to create a view which switches rows and columns of a > dynamically changing table? Sorry, but no. A VIEW is just a saved SELECT statement. If you can't do it in a SELECT statement, you can't do it in a view. And you can't do that. What you're asking for – switching rows and columns – is not the way SQL looks at data. Some SQL engines provide a custom function which does it (e.g. SQL Server) because of how difficult it is to do it without a special function. For SQLite it's going to be easier to do it in your favourite programming language using SQL just to retrieve the data. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is it possible to transpose a table using SQL?
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