• <menu id="ckmo6"></menu>
  • <input id="ckmo6"><u id="ckmo6"></u></input>
    <menu id="ckmo6"></menu>
  • <input id="ckmo6"><acronym id="ckmo6"></acronym></input>
  • 使用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

    相关文章
    相关标签/搜索
    香港精选免费资料大全