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
– MAK, Commented Jan 22, 2015 at 12:22
@Winged Panther, That is the problem I am facing here. In SQL Server I do it using Stuff. — MAK
– MAK, Commented Jan 22, 2015 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
– MAK, Commented Jan 22, 2015 at 12:26
@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
– Erwin Brandstetter, Commented Jan 27, 2015 at 11:51

Erwin Brandstetter · Accepted Answer · 2017-12-06 12:23:00Z

6

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 6, 2017 at 12:23

answered Jan 26, 2015 at 11:57

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

MAK Over a year ago

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.

Erwin Brandstetter Over a year ago

@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: stackoverflow.com/questions/15506199/…

MAK Over a year ago

This link : cureffi.org/2013/03/19/… 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?

Erwin Brandstetter Over a year ago

@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.

Sam Over a year ago

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);

|

Peter · Accepted Answer · 2019-04-05 14:49:52Z

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;

Muhammad Dyas Yaskur · Accepted Answer · 2021-02-15 00:28:13Z

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!

Collectives™ on Stack Overflow

Dynamic pivot query using PostgreSQL 9.3

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related