New answers tagged view
Following sample may be useful to build query which you want to write. USE tempdb GO CREATE TABLE t_Ids (id VARCHAR(10) PRIMARY KEY ) GO INSERT INTO t_Ids VALUES('id1'),('id2'),('id3'),('id4') GO CREATE VIEW v_V1 AS SELECT 'id1' id, 2 value UNION SELECT 'id1' id, 1 UNION SELECT 'id1' id, 3 UNION SELECT 'id2' id, 1 GO CREATE VIEW v_V2 AS SELECT 'id3' ...
To summarize discussion on the topic, creating this sort of "Uber" view with several tables that have matching primary keys and similar (if not the same) row counts is not necessarily a bad idea for the purpose of streamlining code. Additionally, any tables or columns not used in subsequent queries on such a view are dropped from the execution plan in most ...
For the simple case you present, build an array from your sorted values and unnest one by one in the SELECT list SELECT arr AS field1 , arr AS field2 , arr AS field3 FROM (SELECT ARRAY(SELECT value FROM test ORDER BY field) AS arr) sub; Obviously, this is not dynamic, but only works for given fields. You did not actually ask for ...
I think you are looking for something like pivot. PostgreSQL is able to do something like that with a tablefunc. There you could use crosstab() But having said: This is not complete dynamic and therefore not fitting your question 100%.
it is not possible to have dynamic column lists. at the time you issue a SELECT the target list has to be clear. so you cannot just add a column somewhere just because some value is added.
I think your view solution could be appropriate in a scenario where you would vertically partition the data, i.e. where all tables contain the same columns and the view would merely UNION those rows with check constraints. However, what you're proposing is a view that joins a large number of tables together, i.e. partitioning horizontally. Even if we assume ...
Top 50 recent answers are included