Table-valued functions as PIVOT

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Table-valued functions as PIVOT

Gary Briggs
It's been a while since anyone has brought up PIVOT, and none that I
can see since table-valued functions were added [in 3.9.0, I think?]

Has anyone successfully implemented PIVOT using table-valued functions?

I'm in the situation again where I'd like a better way to pivot, where
knowledge of possible values in a column can be delayed to execution-time
rather than needing to be known at query-writing time.

A notional example [which is missing considerate of other columns,
which might really be this idea's downfall] might look like:

SELECT * FROM person;
name   | age
------------
Alice  | 42
Bob    | 27
Eve    | 16

CREATE TABLE pers_piv(person, name);
SELECT * FROM pers_piv; -- Alice, Bob, Eve, conveniently hidden by *

Alice | Bob | Eve
-----------------
42    | 27  | 16


Cheers,
Gary
_______________________________________________
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: Table-valued functions as PIVOT

Richard Hipp-3
On 9/14/17, Gary Briggs <[hidden email]> wrote:

> It's been a while since anyone has brought up PIVOT, and none that I
> can see since table-valued functions were added [in 3.9.0, I think?]
>
> Has anyone successfully implemented PIVOT using table-valued functions?
>
> I'm in the situation again where I'd like a better way to pivot, where
> knowledge of possible values in a column can be delayed to execution-time
> rather than needing to be known at query-writing time.
>
> A notional example [which is missing considerate of other columns,
> which might really be this idea's downfall] might look like:
>
> SELECT * FROM person;
> name   | age
> ------------
> Alice  | 42
> Bob    | 27
> Eve    | 16
>
> CREATE TABLE pers_piv(person, name);

The syntax would need to be:

   CREATE VIRTUAL TABLE pers_piv USING pivot(person,name);

The resulting pers_piv table would be a pivot of the person table for
a snapshot in time.  In other words, pers_piv would not track
subsequent changes in the original person table.

Implementing a pivot virtual table such as described above would not
be too hard.  I encourage you to give it a try and publish the result.


> SELECT * FROM pers_piv; -- Alice, Bob, Eve, conveniently hidden by *
>
> Alice | Bob | Eve
> -----------------
> 42    | 27  | 16
>
>
> Cheers,
> Gary
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
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: [EXTERNAL] Re: Table-valued functions as PIVOT

Hick Gunter
Should not the result (for a simple pivot) be more like

Field | Alice | Bob | Eve
 -----------------
age   | 42    | 27  | 16

with one row for each column of the original table?

Or maybe even:

Using pivot (<table_name>,<pivot_field>,<expression_list>);


-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Richard Hipp
Gesendet: Donnerstag, 14. September 2017 18:53
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] Table-valued functions as PIVOT

On 9/14/17, Gary Briggs <[hidden email]> wrote:

> It's been a while since anyone has brought up PIVOT, and none that I
> can see since table-valued functions were added [in 3.9.0, I think?]
>
> Has anyone successfully implemented PIVOT using table-valued functions?
>
> I'm in the situation again where I'd like a better way to pivot, where
> knowledge of possible values in a column can be delayed to
> execution-time rather than needing to be known at query-writing time.
>
> A notional example [which is missing considerate of other columns,
> which might really be this idea's downfall] might look like:
>
> SELECT * FROM person;
> name   | age
> ------------
> Alice  | 42
> Bob    | 27
> Eve    | 16
>
> CREATE TABLE pers_piv(person, name);

The syntax would need to be:

   CREATE VIRTUAL TABLE pers_piv USING pivot(person,name);

The resulting pers_piv table would be a pivot of the person table for a snapshot in time.  In other words, pers_piv would not track subsequent changes in the original person table.

Implementing a pivot virtual table such as described above would not be too hard.  I encourage you to give it a try and publish the result.


> SELECT * FROM pers_piv; -- Alice, Bob, Eve, conveniently hidden by *
>
> Alice | Bob | Eve
> -----------------
> 42    | 27  | 16
>
>
> Cheers,
> Gary
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
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: Table-valued functions as PIVOT

petern
In reply to this post by Gary Briggs
Richard is right.  Unless you're discarding transformed rowsets, your
pivots will become stale.

If you need a live pivot, I recently disclosed a SQLite native language
"stored procedure"  for computing live pivot views on this mailing list:

http://sqlite.1065341.n5.nabble.com/Generalized-SQLite-stored-procedure-style-pivot-table-exhibit-td95724.html

Further to your exact requirement, you would simply feed into the
generate_pivot procedure the group_concat() aggregate instead of sum().


On Thu, Sep 14, 2017 at 9:37 AM, Gary Briggs <[hidden email]> wrote:

> It's been a while since anyone has brought up PIVOT, and none that I
> can see since table-valued functions were added [in 3.9.0, I think?]
>
> Has anyone successfully implemented PIVOT using table-valued functions?
>
> I'm in the situation again where I'd like a better way to pivot, where
> knowledge of possible values in a column can be delayed to execution-time
> rather than needing to be known at query-writing time.
>
> A notional example [which is missing considerate of other columns,
> which might really be this idea's downfall] might look like:
>
> SELECT * FROM person;
> name   | age
> ------------
> Alice  | 42
> Bob    | 27
> Eve    | 16
>
> CREATE TABLE pers_piv(person, name);
> SELECT * FROM pers_piv; -- Alice, Bob, Eve, conveniently hidden by *
>
> Alice | Bob | Eve
> -----------------
> 42    | 27  | 16
>
>
> Cheers,
> Gary
> _______________________________________________
> 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