使用 PostgreSQL 9.3 的动态数据透视查询 [英] Dynamic pivot query using PostgreSQL 9.3

问题描述

我有一个名为 Product 的表:

I have a table named as Product:

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

样本值:

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

我想动态生成 Salescountry's 名称并显示该国家/地区 SalesQuantity 销售的总和.

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

预期结果:

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

我是用 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);

如何在 Postgres 中实现这一点?

How to achieve this in Postgres?

推荐答案

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

详细说明:

• PostgreSQL 交叉表查询
• 使用 Tablefunc 在多列上透视

完全动态查询不同数量的不同Salescountry?

Completely dynamic query for varying number of distinct Salescountry?

• 使用 CASE 和分组依据

这篇关于使用 PostgreSQL 9.3 的动态数据透视查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！