pivot

MySQL pivot table query with dynamic columns

by

The only way in MySQL to do this dynamically is with Prepared statements. Here is a good article about them: Dynamic pivot tables (transform rows to columns) Your code would look like this: SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( ‘MAX(IF(pa.fieldname=””‘, fieldname, ”’, pa.fieldvalue, NULL)) AS ‘, fieldname ) ) INTO @sql FROM product_additional; SET … Read more

PostgreSQL Crosstab Query

by

Install the additional module tablefunc once per database, which provides the function crosstab(). Since Postgres 9.1 you can use CREATE EXTENSION for that: CREATE EXTENSION IF NOT EXISTS tablefunc; Improved test case CREATE TABLE tbl ( section text , status text , ct integer — “count” is a reserved word in standard SQL ); INSERT … Read more

MySQL pivot row into dynamic number of columns

by

Unfortunately MySQL does not have a PIVOT function which is basically what you are trying to do. So you will need to use an aggregate function with a CASE statement: select pt.partner_name, count(case when pd.product_name=”Product A” THEN 1 END) ProductA, count(case when pd.product_name=”Product B” THEN 1 END) ProductB, count(case when pd.product_name=”Product C” THEN 1 END) … Read more

Efficiently convert rows to columns in sql server

by

There are several ways that you can transform data from multiple rows into columns. Using PIVOT In SQL Server you can use the PIVOT function to transform the data from rows to columns: select Firstname, Amount, PostalCode, LastName, AccountNumber from ( select value, columnname from yourtable ) d pivot ( max(value) for columnname in (Firstname, … Read more

SQL Server dynamic PIVOT query?

by

Dynamic SQL PIVOT: create table temp ( date datetime, category varchar(3), amount money ) insert into temp values (‘1/1/2012’, ‘ABC’, 1000.00) insert into temp values (‘2/1/2012’, ‘DEF’, 500.00) insert into temp values (‘2/1/2012’, ‘GHI’, 800.00) insert into temp values (‘2/10/2012’, ‘DEF’, 700.00) insert into temp values (‘3/1/2012’, ‘ABC’, 1100.00) DECLARE @cols AS NVARCHAR(MAX), @query AS … Read more

How can I return pivot table output in MySQL?

by

This basically is a pivot table. A nice tutorial on how to achieve this can be found here: http://www.artfulsoftware.com/infotree/qrytip.php?id=78 I advise reading this post and adapt this solution to your needs. Update After the link above is currently not available any longer I feel obliged to provide some additional information for all of you searching … Read more