Is it possible to transpose a table using SQL?

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Is it possible to transpose a table using SQL?

Shane Dev
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to transpose a table using SQL?

Simon Slavin-3
On 31 Mar 2019, at 9:07pm, Shane Dev <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to transpose a table using SQL?

Jean-Luc Hainaut
In reply to this post by Shane Dev
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to transpose a table using SQL?

Keith Medcalf
In reply to this post by Shane Dev

On Sunday, 31 March, 2019 14:07, Shane Dev <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to transpose a table using SQL?

Shane Dev
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 <[hidden email]> wrote:

>
> On Sunday, 31 March, 2019 14:07, Shane Dev <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to transpose a table using SQL?

Dominique Devienne
On Mon, Apr 1, 2019 at 7:15 AM Shane Dev <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to transpose a table using SQL?

Simon Slavin-3
On 1 Apr 2019, at 10:18am, Dominique Devienne <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to transpose a table using SQL?

Jake
In reply to this post by Shane Dev
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users