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:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Richard Hipp Gesendet: Donnerstag, 14. September 2017 18:53 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] Re: [sqlite] Table-valued functions as PIVOT On 9/14/17, Gary Briggs <chu...@icculus.org> 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org 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: h...@scigames.at 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
