Dynamic pivot query using PostgreSQL 9.3
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 Salescountry
?
- Dynamic alternative to pivot with CASE and GROUP BY
While it's a two step process, this method will create a pivot with dynamic columns, without the need to specify the result set and without creating temp tables.
First we define a function that creates a dynamically prepared statement:
CREATE OR REPLACE FUNCTION flowms.pivotcode_sql(
tablename character varying,
rowc character varying,
colc character varying,
cellc character varying,
celldatatype character varying)
RETURNS character varying
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
declare
dynsql1 varchar;
dynsql2 varchar;
columnlist varchar;
begin
-- 1. retrieve list of column names.
dynsql1 = 'select string_agg(distinct ''"''||'||colc||'||''" '||celldatatype||''','','' order by ''"''||'||colc||'||''" '||celldatatype||''') from '||tablename||';';
execute dynsql1 into columnlist;
-- 2. set up the crosstab query
--tablename = REPLACE(text, ''', E'\\"')
dynsql2 = 'prepare crosstab_stmt as select * from crosstab (
''select '||rowc||','||colc||','||cellc||' from '||replace(tablename, chr(39),E'\'\'')||' group by 1,2 order by 1,2'',
''select distinct '||colc||' from '||replace(tablename, chr(39),E'\'\'')||' order by 1''
)
as newtable (
'||rowc||' varchar,'||columnlist||'
);';
deallocate all;
execute dynsql2;
return dynsql2;
end
$BODY$;
You can now call the function
select pivotcode_sql('tablename', 'rowfield', 'columnfield', 'sum(value)', 'integer');
which will create the prepared statement. Next you can execute the prepared statement:
execute crosstab_stmt;