On 17 Jun 2017, at 14:25, Igor Tandetnik wrote: > By the same token, I don't believe such a query exists.
Hi, I think such a query exists, here we go: Columns are: col_id, value, rec_id col-1 1 1 col-1 2 2 col-1 3 3 col-1 4 4 col-1 5 5 col-1 6 6 col-1 7 7 col-1 8 8 col-1 9 9 col-2 1 1 col-2 2 2 col-2 3 3 col-2 4 4 col-2 5 5 col-3 1 1 col-4 1 1 col-5 1 1 col-6 1 1 col-7 1 1 col-8 1 1 col-9 1 1 Which is a classical entity-attribute-value (EAV) model, taking normalization to the extreme and no longer uses columns in the traditional way. Instead, every single value is stored in its own row. Besides the value, the row also has a column to specify which attribute the value represents and a third column to identify what entity the values belongs to. Ultimately, a three column table can hold any data without ever needing to change the table definition. To get a traditional VIEW on this, we want to transpose the data, or create a pivot of it. Here is a pretty simple structure of such a VIEW for the first three columns: select rec_id , max(case when col_id = "col-1" then value end) col1 , max(case when col_id = "col-2" then value end) col2 , max(case when col_id = "col-3" then value end) col3 from products group by rec_id With columns: rec_id, col1, col2, col3 1 1 1 1 2 2 2 3 3 3 4 4 4 5 5 5 6 6 7 7 8 8 9 9 That's what I need to avoid schema changes on tables with dynamic attributes and make it compatible with the session extension. -- Robert M. Münch, CEO M: +41 79 65 11 49 6 Saphirion AG smarter | better | faster http://www.saphirion.com http://www.nlpp.ch
signature.asc
Description: OpenPGP digital signature
_______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
