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] FTS5 Transaction Leads to NULL Pointer
On Tue, 19 Mar 2019 at 07:35, Dan Kennedy wrote: > > Now fixed here: > >https://sqlite.org/src/info/45c73deb440496e8 From that diff, it seems that you changed the documentation of the function's parameters where the function was defined (see fts5_hash.c line 489 at the right) but not where the prototype was declared (see fts5Int.h line 588 at the right, which still talks about a "pointer to doclist"). ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3 (CLI, linux/Ubuntu)
Works fine for me on Centos ... using the default (ancient) version of SQLite3 # sqlite3 /tmp/test.db "CREATE TABLE test (col1); INSERT INTO test VALUES(1);" # touch /tmp/test.init # sqlite3 /tmp/test.db "SELECT * FROM test;" 2>/dev/null 1 # sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM test;" 2>/dev/null 1 [root@mail ~]# sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM test;" -- Loading resources from /tmp/test.init 1 # sqlite3 --version 3.6.20 # and with the current tip of trunk # ./sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM test;" -- Loading resources from /tmp/test.init 1 # ./sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM test;" 2>/dev/null 1 # ./sqlite3 --version 3.28.0 2019-03-31 18:17:00 d03b611302f68483770d49b113b4ed685ba03526d2007647c306f8ec7ae697d2 --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of >softw...@quantentunnel.de >Sent: Saturday, 30 March, 2019 05:28 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Bug in sqlite3 (CLI, linux/Ubuntu) > >Dear colleagues > >I detected an unexpected behaviour in sqlite3 (CLI) that I consider a >bug as it seems not documented. > >When using an init file (even if an empty file), sqlite3 outputs an >extra empty line to stdout. This messes up parsing of the sqlite3 >output, as this line is not present in the absence of an init file: > >buero:~$ sqlite3 /tmp/test.db "CREATE TABLE test (col1); INSERT INTO >test VALUES(1);" >buero:~$ touch /tmp/test.init >buero:~$ ls -l /tmp/test.* >-rw-r- 1 abc abc 2048 Mär 30 12:17 /tmp/test.db >-rw-r- 1 abc abc0 Mär 30 12:17 /tmp/test.init >buero:~$ sqlite3 /tmp/test.db "SELECT * FROM test;" 2>/dev/null >1 >buero:~$ sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM >test;" 2>/dev/null > >1 >buero:~$ > > >My configuration >buero:~$ sqlite3 --version >3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f >buero:~$ uname -a >Linux buero 4.15.0-46-generic #49~16.04.1-Ubuntu SMP Tue Feb 12 >17:45:24 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux >___ >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] Bug in sqlite3 (CLI, linux/Ubuntu)
> Dear colleagues
>
> I detected an unexpected behaviour in sqlite3 (CLI) that I consider a bug as
> it seems not documented.
>
> When using an init file (even if an empty file), sqlite3 outputs an extra
> empty line to stdout. This messes up parsing of the sqlite3 output, as this
> line is not present in the absence of an init file:
>
> buero:~$ sqlite3 /tmp/test.db "CREATE TABLE test (col1); INSERT INTO test
> VALUES(1);"
> buero:~$ touch /tmp/test.init
> buero:~$ ls -l /tmp/test.*
> -rw-r- 1 abc abc 2048 Mär 30 12:17 /tmp/test.db
> -rw-r- 1 abc abc0 Mär 30 12:17 /tmp/test.init
> buero:~$ sqlite3 /tmp/test.db "SELECT * FROM test;" 2>/dev/null
> 1
> buero:~$ sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM test;"
> 2>/dev/null
>
> 1
> buero:~$
>
>
> My configuration
> buero:~$ sqlite3 --version
> 3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f
> buero:~$ uname -a
> Linux buero 4.15.0-46-generic #49~16.04.1-Ubuntu SMP Tue Feb 12 17:45:24 UTC
> 2019 x86_64 x86_64 x86_64 GNU/Linux
> ___
... [show rest of quote]
upgrade ?
~$ sqlite3 /tmp/test.db "CREATE TABLE test (col1); INSERT INTO test
VALUES(1);"
~$
~$ touch /tmp/test.init
~$ sqlite3 /tmp/test.db "SELECT * FROM test;" 2>/dev/null
1
~$
~$
~$ sqlite3 -int /tmp/test.init /tmp/test.db "SELECT * FROM test;"
2>/dev/null
~$ ls -l /tmp
total 16
-rw-r--r-- 1 luuk luuk 8192 Mar 30 17:18 test.db
-rw-rw-rw- 1 luuk luuk0 Mar 30 17:18 test.init
~$ sqlite3 -version
3.22.0 2018-01-22 18:45:57
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1
~$
Hi Luuk
Thanks for looking into it. There is a typo in your example (-int instead of
-init); the error message ("sqlite3: Error: unknown option: -int") went to
/dev/null. Thus, there is no output to stdout; also the expected query result
'1' is lacking.
Before I manually upgrade (I use currently the latest automatically uogradable
version in Ubuntu xenial): would it be possible that you re-run the example
(with -init) to check whether the leading empty line is indeed no longer
present in version 3.22?
___
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
