<address id="xvfrz"></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

              相关文章
              相关标签/搜索
              2019年所有开马的记录