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
