sql - PostgreSQL中的转置表

我在PostgreSQL中有一个表，比如：

org_name | month_1 | month_2 | ... | month_12
---------------------------------------------
org1     |  20     |   30    | ... |  15
org2     |  34     |   27    | ... |  49

我需要把它转换成：

month  |  org1 | org2
----------------------
   1   |   20  | 34
   2   |   30  | 27
..     |   ..  | ..
  12   |   15  | 49

我在stackoverflow上找到了下一个解决方案：

SELECT
    *
FROM
    (select org_name, monthes, value
    from my_table
    unpivot
    (
        value
        for monthes in (month_2, month_3, month_4, month_5, month_6, month_7, month_8, month_9, month_10, month_11, month_12)
    ) unpiv
    ) src
    pivot
    (
        sum(value)
        for org_name in ('org1', 'org2')
    ) piv

但它不能处理关于“for”的语法错误。我错在哪里了？

最佳答案

我还没能得到你的工作方法，但这种多步骤的联合似乎是。

cte (mo, Org1, Org2) as (
SELECT 1, case when org_name = 'org1' then month_1 end as Org1, case when org_name = 'org2' then month_1 end as Org2 from myTable UNION ALL
SELECT 2, case when org_name = 'org1' then month_2 end as Org1, case when org_name = 'org2' then month_2 end as Org2 from myTable UNION ALL
SELECT 3, case when org_name = 'org1' then month_3 end as Org1, case when org_name = 'org2' then month_3 end as Org2 from myTable UNION ALL
...
SELECT 12, case when org_name = 'org1' then month_12 end as Org1, case when org_name = 'org2' then month_12 end as Org2 from myTable)
SELECT mo, max(org1) org1, max(org2) org2
FROM  cte
GROUP BY mo

也许你会发现最不明显的：

With myTable (org_name, month_1, Month_2, month_3, Month_12) as (
    Select 'org1',20,30,40,15 union all
    Select 'org2',34,27,45,49),

cte  (Mo,Org1,org2) as(select unnest(ARRAY[1,2,3,12]) AS Mo
     , case when org_name='org1' then unnest(ARRAY[month_1, Month_2,month_3,Month_12]) end as Org1
     , case when org_name='org2' then unnest(ARRAY[month_1, Month_2,month_3,Month_12]) end as Org2
     from mytable)

     Select mo,sum(org1) org1, sum(org2) org2
     From cte
     group by mo

关于sql - PostgreSQL中的转置表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/46281584/