<address id="rf17h"><dfn id="rf17h"></dfn></address>
    <address id="rf17h"><var id="rf17h"></var></address> <sub id="rf17h"><var id="rf17h"><ins id="rf17h"></ins></var></sub><address id="rf17h"></address>

    <address id="rf17h"><listing id="rf17h"><ins id="rf17h"></ins></listing></address>

    <sub id="rf17h"><var id="rf17h"></var></sub>
    <sub id="rf17h"><var id="rf17h"><output id="rf17h"></output></var></sub>

      <address id="rf17h"></address>
      <sub id="rf17h"><var id="rf17h"></var></sub>
          <sub id="rf17h"><dfn id="rf17h"><ins id="rf17h"></ins></dfn></sub>

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

          相关文章
          相关标签/搜索
          王中王中特免费公开资料选料