Consulta dinámica dinámica usando PostgreSQL 9.3

pivot crosstab

Question

Tengo una tabla llamada como Product :

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

Valores de muestra:

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

Quiero generar Salescountry''s nombres Salescountry''s y mostrar la suma de las SalesQuantity de SalesQuantity en ese país.

Resultado Esperado:

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

Lo hice usando 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);

¿Cómo lograr esto en Postgres?

Answer 1

Si bien es un proceso de dos pasos, este método creará un pivote con columnas dinámicas, sin la necesidad de especificar el conjunto de resultados y sin crear tablas temporales.

Primero definimos una función que crea una declaración preparada dinámicamente:

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

Ahora puedes llamar a la función

select pivotcode_sql(''tablename'', ''rowfield'', ''columnfield'', ''sum(value)'', ''integer'');

que creará la declaración preparada. A continuación, puede ejecutar la declaración preparada:

execute crosstab_stmt;

Answer 2

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

Explicación detallada:

• PostgreSQL Crosstab Query
• Pivote en múltiples columnas usando Tablefunc

¿ Salescountry completamente dinámica para un número variable de países de Salescountry distintos?

• Alternativa dinámica al pivote con CASE y GROUP BY