postgresql - 排序查询 Crosstab Postgres

Question

我正在尝试在 Postgres 中旋转一个表。我的表“示例”有很多列(代码、部门、项目、年份、期间、值、预购)，而不是每行有 1 个概念、1 年、1 个值，我想要按年显示值的概念。从此；

Item            Value   Year    PreOrder
Sales             50    2011    1
Costs            -20    2011    2
GrossProfit       30    2011    3
Expenses          -5    2011    4
Tax               -3    2011    5
Profit            22    2011    6
Sales             45    2012    3
Costs            -20    2012    4
GrossProfit       25    2012    5
Expenses          -5    2012    6
Tax               -3    2012    7
Profit            17    2012    8

对此:

Item        2011    2012
Sales         50    45
Costs        -20    -20
GrossProfit   30    25
Expenses      -5    -5
Tax           -3    -3
Profit        22    17

在 Postgres 中使用交叉表:

Select * from crosstab($$Select item, year, value from sec_sample
    Where cik=320193
    AND period='Annual'
    AND Sector='Agro'
    Order by year, preorder
    $$,
    $$VALUES ('2011'::int), ('2012')$$)
AS value("item" varchar(255), "2011" numeric(20,2), "2012" numeric(20,2));

但是这会导致:

Item        2011    2012
Sales         50    
Costs        -20    
GrossProfit   30    
Expenses      -5    
Tax           -3    
Profit        22    
Sales               45
Costs              -20
GrossProfit         25
Expenses            -5
Tax                 -3
Profit              17

知道如何修改我的查询吗？谢谢

Answer 1

AFAIK，要按 item 分组，您必须按 item 排序:

Select * from crosstab($$Select item, year, value from sec_sample
    Where cik=320193
    AND period='Annual'
    AND Sector='Agro'
    Order by item, year, preorder
    $$,
    $$VALUES ('2011'::int), ('2012')$$)
AS value("item" varchar(255), "2011" numeric(20,2), "2012" numeric(20,2));

但是您可以在不使用crosstab 的情况下进行透视，如下所示:

select
    s.item,
    sum(case when s.year = 2011 then s.value else 0 end) as "2011",
    sum(case when s.year = 2012 then s.value else 0 end) as "2012"
from sec_sample as s
group by s.item
order by min(s.preorder)

我认为修改这种类型的查询更容易

sql fiddle demo