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=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
