You cannot do it with SQL (except with dynamic queries), unless you know the number of columns (i. e. questions) in design time. You should pull the data you want in tabular format and then process it on client side: SELECT * FROM Question LEFT OUTER JOIN Response ON Response.QuestionId = Question.QuestionID or, probably, this … Read more
The number and names of columns must be fixed at the time you prepare the query. That’s just the way SQL works. So you have two choices of how to solve this. Both choices involve writing application code: (1) Query the distinct values of way and then write code to use these to construct the … Read more
What you ask for is impossible. SQL is a strictly typed language. PostgreSQL functions need to declare a return type (RETURNS ..) at the time of creation. A limited way around this is with polymorphic functions. If you can provide the return type at the time of the function call. But that’s not evident from … Read more
You can use the provided C function crosstab_hash for this. The manual is not very clear in this respect. It’s mentioned at the end of the chapter on crosstab() with two parameters: You can create predefined functions to avoid having to write out the result column names and types in each query. See the examples … Read more
Basing my answer on a table of the form: CREATE TABLE tbl ( sl_no int , username text , designation text , salary int ); Each row results in a new column to return. With a dynamic return type like this, it’s hardly possible to make this completely dynamic with a single call to the … Read more
SELECT * FROM crosstab ( ‘SELECT ProductNumber, ProductName, Salescountry, SalesQuantity FROM product ORDER BY 1’ , $$SELECT unnest(‘{US,UK,UAE1}’::varchar[])$$ ) AS ct ( “ProductNumber” varchar , “ProductName” varchar , “US” int , “UK” int , “UAE1” int); Detailed explanation: PostgreSQL Crosstab Query Pivot on Multiple Columns using Tablefunc Completely dynamic query for varying number of distinct … Read more
Use crosstab() from the tablefunc module. SELECT * FROM crosstab( $$SELECT user_id, user_name, rn, email_address FROM ( SELECT u.user_id, u.user_name, e.email_address , row_number() OVER (PARTITION BY u.user_id ORDER BY e.creation_date DESC NULLS LAST) AS rn FROM usr u LEFT JOIN email_tbl e USING (user_id) ) sub WHERE rn < 4 ORDER BY user_id $$ , … Read more
First compute the average with the aggregate function avg(): SELECT neighborhood, bedrooms, avg(price) FROM listings GROUP BY 1,2 ORDER BY 1,2; Then feed the result to the crosstab() function as instructed in great detail in this related answer: PostgreSQL Crosstab Query
The problem with your query is that b and c share the same timestamp 2012-01-02 00:00:00, and you have the timestamp column timeof first in your query, so – even though you added bold emphasis – b and c are just extra columns that fall in the same group 2012-01-02 00:00:00. Only the first (b) … Read more
If you have not installed the additional module tablefunc, run this command once per database: CREATE EXTENSION tablefunc; Answer to question A very basic crosstab solution for your case: SELECT * FROM crosstab( ‘SELECT bar, 1 AS cat, feh FROM tbl_org ORDER BY bar, feh’) AS ct (bar text, val1 int, val2 int, val3 int); … Read more