Dynamic pivot query using PostgreSQL 9.3

Question

I have a table named as Product:

create table product (
    ProductNumber varchar(10),
    ProductName varchar(10),
    SalesQuantity int,
    Salescountry varchar(10)
);

Sample values:

insert into product values
  ('P1', 'PenDrive', 50,  'US')
, ('P2', 'Mouse',    100, 'UK')
, ('P3', 'KeyBoard', 250, 'US')
, ('P1', 'PenDrive', 300, 'US')
, ('P2', 'Mouse',    450, 'UK')
, ('P5', 'Dvd',      50,  'UAE');

I want to generate the Salescountry's names dynamically and show the sum of SalesQuantity sale in that Country.

Expected result:

ProductName US    UK    UAE
----------------------------
PenDrive    350   0     0
Mouse       0     550   0
KeyBoard    250   0     0
Dvd         0     0     50

I did it using SQL Server 2008 R2:

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(SalesCountry) 
            FROM Product
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ProductName, ' + @cols + ' from 
            (
                select ProductName
                    , SalesQuantity as q
                    , Salescountry
                from Product
           ) x
            pivot 
            (
                 SUM(q)
                for Salescountry in (' + @cols + ')
            ) p '

PRINT(@query);
execute(@query);

How to achieve this in Postgres?

@Winged Panther, `Case` is not a good advice for dynamic query. — MAK, Jan 22 '15 at 12:22
@Winged Panther, That is the problem I am facing here. In SQL Server I do it using `Stuff`. — MAK, Jan 22 '15 at 12:23
@WingedPanther, Yeah! I referred that also. But there is a `column list` given. But here I want it should be dynamic(SalesCountry). — MAK, Jan 22 '15 at 12:26
Check [this](http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/) — Pusker György, Jan 22 '15 at 13:03
@PuskerGyörgy, Awesome post by Eric Vallabh Minikel. Thank you so much for sharing. — MAK, Jan 22 '15 at 13:12
@PuskerGyörgy: A word of warning: The code in the linked blog does not escape identifiers, which breaks with non-standard names and allows SQL injection. — Erwin Brandstetter, Jan 27 '15 at 11:51

Erwin Brandstetter · Accepted Answer · 2017-12-06T12:23:00.003

5

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:

Completely dynamic query for varying number of distinct Salescountry?

Dynamic alternative to pivot with CASE and GROUP BY

edited Dec 06 '17 at 12:23

answered Jan 26 '15 at 11:57

Erwin Brandstetter

605,456
145
1,078
1,228

1

I don't want to give `Salescountry` directly into `unnest()` and in `columnlist`. I want that to get dynamically as I have done in SQL Server shown in the post using `@cols = stuff`. – MAK Jan 26 '15 at 12:11
@MAK: A completely dynamic query is hardly possible in a single call. You need to build a dynamic statement and execute that in a second call. Details here: http://stackoverflow.com/questions/15506199/dynamic-alternative-to-pivot-with-case-and-group-by/15514334#15514334 – Erwin Brandstetter Jan 26 '15 at 12:17
This link : http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/ Gives me the complete `crosstab` query But the problem is that, How can I give the column definition list while returning the result from the function. Because the `Salescountry` might me number of countries and that may changes from the front end as they insert the new country's into that. How can I return the dynamic `column definition list` from the function? – MAK Jan 27 '15 at 06:07
@MAK. You can't. That's why you need the two steps I mentioned. SQL demands to know the return type. The blog you link to does as much: concatenate a statement using the function in the first call, execute the statement in the second call. (BTW, it is **unsafe**, open to SQL injection!) There are a number of approaches, but none of them works with a single call. You *could* return arrays instead, that's possible in a single call. Read the link I added. – Erwin Brandstetter Jan 27 '15 at 11:44
The correct syntax would rather be like below. There's an issue with how the brackets have been put. SELECT * FROM crosstab( 'SELECT ProductNumber, ProductName, Salescountry, SalesQuantity FROM product ORDER BY 1' $$SELECT unnest('{US,UK,UAE}'::varchar[])$$ AS ct ( "ProductNumber" varchar ,"ProductName" varchar ,"US" int ,"UK" int) ,"UAE" int); – Sam Dec 06 '17 at 05:48
@Sam: Thanks for pointing out, there were multiple typos. Fixed now. – Erwin Brandstetter Dec 06 '17 at 12:23

score 4 · Answer 2 · answered Apr 05 '19 at 14:49

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;

score 1 · Answer 3 · edited Feb 15 '21 at 00:28

I just improved Peter's solution:

CREATE OR REPLACE FUNCTION export.pivotcode_sql(
    tablename character varying,
    rowc character varying,
    colc character varying,
    cellc character varying,
    celldatatype character varying,
    tblname character varying)
    RETURNS character varying
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
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 = 'drop table if exists '||  tblname  ||' ; create table ' || tblname   ||   ' 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$;

ALTER FUNCTION export.pivotcode_sql(character varying, character varying, character varying, character varying, character varying, character varying)
    OWNER TO postgres;

as in use :

select EXPORT.pivotcode_sql('EXPORT.TERMINAL_REVENUE',  'terminal_no','period', 'sum(amount)', 'numeric(12,2)','faydin.test15');

it creates the table faydin.test15 :

select * from faydin.test15

I hope it helps you as much as it did to me!

Dynamic pivot query using PostgreSQL 9.3

3 Answers3

Linked

Related