使用PostgreSQL 9.3进行动态数据透视查询

        我有一个名为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的名称,并显示该国家的SalesQuantity销售总额.

        预期结果:

        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实现这一目标?

        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 Crosstab Query
        > Pivot on Multiple Columns using Tablefunc

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

        > Dynamic alternative to pivot with CASE and GROUP BY

        相关文章
        相关标签/搜索
        黄大仙选黄大仙一码一肖