create table vote (Photo integer, Voter text, Decision text); insert into vote values (1, ‘Alex’, ‘Cat’), (1, ‘Bob’, ‘Dog’), (1, ‘Carol’, ‘Cat’), (1, ‘Dave’, ‘Cat’), (1, ‘Ed’, ‘Cat’), (2, ‘Alex’, ‘Cat’), (2, ‘Bob’, ‘Dog’), (2, ‘Carol’, ‘Cat’), (2, ‘Dave’, ‘Cat’), (2, ‘Ed’, ‘Dog’), (3, ‘Alex’, ‘Horse’), (3, ‘Bob’, ‘Horse’), (3, ‘Carol’, ‘Dog’), (3, ‘Dave’, ‘Horse’), … Read more
This type of transformation is called a pivot. You did not specify what database you are using so I will provide a answers for SQL Server and MySQL. SQL Server: If you are using SQL Server 2005+ you can implement the PIVOT function. If you have a known number of values that you want to … Read more
The main difference between the two is the pivot_table expects your input data to already be a DataFrame; you pass a DataFrame to pivot_table and specify the index/columns/values by passing the column names as strings. With cross_tab, you don’t necessarily need to have a DataFrame going in, as you just pass array-like objects for index/columns/values. … Read more
You can do this with crosstab() from the additional module tablefunc: SELECT b , COALESCE(a1, 0) AS “A1” , COALESCE(a2, 0) AS “A2” , COALESCE(a3, 0) AS “A3” , … — all the way up to “A30” FROM crosstab( ‘SELECT colb, cola, 1 AS val FROM matrix ORDER BY 1,2’ , $$SELECT ‘A’::text || g … 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
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
Consider the following demo: CREATE TEMP TABLE qa (id int, usr int, question_id int, answer_id int); INSERT INTO qa VALUES (1,1,1,1) ,(2,1,2,9) ,(3,1,3,15) ,(4,2,1,2) ,(5,2,2,12) ,(6,2,3,20); SELECT * FROM crosstab(‘ SELECT usr::text ,question_id ,answer_id FROM qa ORDER BY 1,2’) AS ct ( usr text ,q1 int ,q2 int ,q3 int); Result: usr | q1 | … Read more
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