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