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
