使用 2+ 列旋轉（使用 CROSSTAB？）

我有一個deflator定義為的表：

              Table "deflator"
   Column   |       Type        | Modifiers
-------------+-------------------+-----------
country_code | smallint          | not null
country_name | character varying | not null
year         | smallint          | not null
deflator     | numeric           |
source       | character varying |

此表的範例輸出如下所示：

country_code | country_name  | year | deflator | source
-------------+---------------+------+----------+----------
          1 | country_1     | 2016 |       12 | source_1
          1 | country_1     | 2015 |       11 | source_2
          1 | country_1     | 2014 |       10 | source_2
          2 | country_2     | 2016 |       15 | source_1
          2 | country_2     | 2015 |       14 | source_1
          2 | country_2     | 2014 |       13 | source_2
          3 | country_3     | 2016 |       18 | source_1
          3 | country_3     | 2015 |       17 | source_2
          3 | country_3     | 2014 |       16 | source_3
(9 rows)

如果我排除列source，我使用以下查詢來透視表：

SELECT
   *
FROM CROSSTAB (
   'SELECT
       country_code
       , country_name
       , year
       , deflator
    FROM dimension.master_oecd_deflator
    ORDER BY 1;'
    , $$ VALUES ('2014'::TEXT), ('2015'::TEXT), ('2016'::TEXT) $$
) AS "ct" (
   "country_code" SMALLINT
   , "country_name" TEXT
   , "2014" NUMERIC
   , "2015" NUMERIC
   , "2016" NUMERIC
);

上面的查詢給了我：

country_code |   country_name    | 2016 | 2015 | 2014 |
-------------+-------------------+------+--- --+------+
          1 | country_1         | 12   | 11   | 10   |
          2 | country_2         | 15   | 14   | 13   |
          3 | country_3         | 18   | 17   | 16   |

但是由於每個國家的平減指數的來源每年都不同，我想source在樞軸中包含該列，以使我想要的輸出看起來像：

country_code |   country_name    | 2016 | 2016_source | 2015 | 2015_source | 2014 | 2014_source
-------------+-------------------+------+-------------+------+-------------+------+------------
          1 | country_1         | 12   | source_1    | 11   | source_2    | 10   | source_2
          2 | country_2         | 15   | source_1    | 14   | source_1    | 13   | source_2
          3 | country_3         | 18   | source_1    | 17   | source_2    | 16   | source_3

如何修改此查詢以提供所需的輸出？（每年的來源都列在年份旁邊）。這甚至可能嗎？

是的，這是可能的，這是解決方案：

WITH cte AS 
   ( SELECT  * 
     FROM CROSSTAB 
         ( 'SELECT  country_code, country_name, year, 
                    deflator || '',''|| source
            FROM deflator 
            ORDER BY 1;', 
            $$ VALUES ('2014'::TEXT), ('2015'::TEXT), ('2016'::TEXT) $$
         ) AS "ct" ( "country_code" SMALLINT, 
                     "country_name" TEXT , 
                     "2014" text, "2015" text, "2016" text
                   )
   )
SELECT country_code, country_name, 
      split_part("2014",',',1) AS "2014", 
      split_part("2014",',',2) AS "2014_source", 
      split_part("2015",',',1) AS "2015", 
      split_part("2015",',',2) AS "2015_source", 
      split_part("2016",',',1) AS "2016", 
      split_part("2016",',',2) AS "2016_source" 
FROM cte ;

引用自：https://dba.stackexchange.com/questions/158181