<thead id="xjx1f"></thead>
      <sub id="xjx1f"></sub>

        <address id="xjx1f"></address>

            使用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

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