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 <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); > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
