Section Status Count
A Active 1
A Inactive 2
B Active 4
B Inactive 5
Section Active Inactive
A 1 2
B 4 5
每个数据库只需要安装一次额外模块tablefunc，它提供了函数crosstab()。从Postgres 9.1开始，您可以使用CREATE EXTENSION命令来完成这个操作：
CREATE EXTENSION IF NOT EXISTS tablefunc;
CREATE TABLE tbl (
section text
, status text
, ct integer -- "count" is a reserved word in standard SQL
);
INSERT INTO tbl VALUES
('A', 'Active', 1), ('A', 'Inactive', 2)
, ('B', 'Active', 4), ('B', 'Inactive', 5)
, ('C', 'Inactive', 7); -- ('C', 'Active') is missing
crosstab(text)具有1个输入参数：
SELECT *
FROM crosstab(
'SELECT section, status, ct
FROM tbl
ORDER BY 1,2' -- needs to be "ORDER BY 1,2" here
) AS ct ("Section" text, "Active" int, "Inactive" int);
返回：
分类 | 激活的 | 未激活的 ---------+--------+---------- A | 1 | 2 B | 4 | 5 C | 7 | -- !!
C的错误结果：值7填入了第一列。有时，这种行为是可取的，但不适用于此用例。crosstab(text, text) 带有2个输入参数：
SELECT *
FROM crosstab(
'SELECT section, status, ct
FROM tbl
ORDER BY 1,2' -- could also just be "ORDER BY 1" here
, $$VALUES ('Active'::text), ('Inactive')$$
) AS ct ("Section" text, "Active" int, "Inactive" int);返回：
区域 | 活跃数 | 不活跃数
---------+--------+----------
A | 1 | 2
B | 4 | 5
C | | 7 -- !!
请注意 C 的正确结果。
第二个参数可以是任何返回与列定义顺序匹配的每个属性的一行查询。通常，您会想从底层表中查询不同的属性，如下所示：
'SELECT DISTINCT attribute FROM tbl ORDER BY 1'
这在手册中有说明。
由于在列定义列表中必须拼写出所有列（除了预定义的N()>变体），因此通常更有效的方法是在VALUES表达式中提供一个简短的列表，如下所示：
$$VALUES ('Active'::text), ('Inactive')$$)
或者（非手册中）：
$$SELECT unnest('{Active,Inactive}'::text[])$$ -- short syntax for long lists
我使用dollar quoting来简化引用。
你甚至可以使用 crosstab(text, text) 输出具有不同数据类型的列 - 只要值列的文本表示是目标类型的有效输入即可。这样你就可以拥有不同种类的属性并为各自的属性输出text、date、numeric等。在手册的章节crosstab(text, text)末尾有一个代码示例。
db<>fiddle在这里
处理超出输入行的方式不同 - 重复的行对应相同的("row_name", "category")组合 - 在上面的例子中是(section, status)。
1个参数形式从左到右填充可用值列。超出的值将被丢弃。
先前的输入行胜出。
2个参数形式将每个输入值分配到其专用列中，覆盖任何先前的分配。
后面的输入行胜出。
通常，你不会有重复项。但如果有，请仔细调整排序顺序以满足你的要求 - 并记录正在发生的事情。
或者如果你不在意，就快速得到任意结果。只需了解影响即可。
使用Tablefunc在多个列上进行枢轴 - 还演示了提到的"额外列"
\crosstabview在psql中Postgres 9.6在其默认交互式终端psql中添加了这个元命令。你可以运行作为第一个crosstab()参数的查询，并将其传递给\crosstabview（立即或在下一步）。例如：
db=> SELECT section, status, ct FROM tbl \crosstabview
与上面的结果相似，但它是一个仅在客户端上表示特征。输入行的处理略有不同，因此不需要使用ORDER BY。有关\crosstabview的详细信息，请查看手册。该页面底部提供了更多代码示例。
由psql功能的作者Daniel Vérité提供相关答案：
SELECT section,
SUM(CASE status WHEN 'Active' THEN count ELSE 0 END) AS active, --here you pivot each status value as a separate column explicitly
SUM(CASE status WHEN 'Inactive' THEN count ELSE 0 END) AS inactive --here you pivot each status value as a separate column explicitly
FROM t
GROUP BY section
sum()，最好使用min()或max()，并且不使用ELSE，这对于text也适用。但这与corosstab()具有微妙的不同效果，后者仅针对每个属性使用“第一个”值。只要只有一个，就没关系了。最后，性能也很重要。 crosstab()是用C编写的，并针对此任务进行了优化。 - Erwin Brandstettertablefunc的crosstab()函数 - 您需要每个数据库安装一次。自PostgreSQL 9.1以来，您可以使用CREATE EXTENSION进行安装：CREATE EXTENSION tablefunc;
CREATE TABLE t (Section CHAR(1), Status VARCHAR(10), Count integer);
INSERT INTO t VALUES ('A', 'Active', 1);
INSERT INTO t VALUES ('A', 'Inactive', 2);
INSERT INTO t VALUES ('B', 'Active', 4);
INSERT INTO t VALUES ('B', 'Inactive', 5);
SELECT row_name AS Section,
category_1::integer AS Active,
category_2::integer AS Inactive
FROM crosstab('select section::text, status, count::text from t',2)
AS ct (row_name text, category_1 text, category_2 text);
以下是DB Fiddle:
CREATE EXTENSION tablefunc; you get this error: https://dbfiddle.uk/j8W1CMvI
ERROR: function crosstab(unknown, integer) does not exist
LINE 4: FROM crosstab('select section::text, status, count::text fro...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
使用JSON聚合的解决方案：
CREATE TEMP TABLE t (
section text
, status text
, ct integer -- don't use "count" as column name.
);
INSERT INTO t VALUES
('A', 'Active', 1), ('A', 'Inactive', 2)
, ('B', 'Active', 4), ('B', 'Inactive', 5)
, ('C', 'Inactive', 7);
SELECT section,
(obj ->> 'Active')::int AS active,
(obj ->> 'Inactive')::int AS inactive
FROM (SELECT section, json_object_agg(status,ct) AS obj
FROM t
GROUP BY section
)X
很抱歉这个内容不完整，因为我无法在这里进行测试，但是它可能会让你朝着正确的方向前进。我正在翻译我使用的一些类似查询的东西：
select mt.section, mt1.count as Active, mt2.count as Inactive
from mytable mt
left join (select section, count from mytable where status='Active')mt1
on mt.section = mt1.section
left join (select section, count from mytable where status='Inactive')mt2
on mt.section = mt2.section
group by mt.section,
mt1.count,
mt2.count
order by mt.section asc;
我正在使用的代码是：
select m.typeID, m1.highBid, m2.lowAsk, m1.highBid - m2.lowAsk as diff, 100*(m1.highBid - m2.lowAsk)/m2.lowAsk as diffPercent
from mktTrades m
left join (select typeID,MAX(price) as highBid from mktTrades where bid=1 group by typeID)m1
on m.typeID = m1.typeID
left join (select typeID,MIN(price) as lowAsk from mktTrades where bid=0 group by typeID)m2
on m1.typeID = m2.typeID
group by m.typeID,
m1.highBid,
m2.lowAsk
order by diffPercent desc;
该函数将返回一个typeID，即最高出价和最低要价，以及两者之间的差异（正差异意味着某物可以以低于其销售价格的价格购买）。
Crosstab 函数可在 tablefunc 扩展中使用。您需要为数据库创建此扩展一次。
CREATE EXTENSION tablefunc;
您可以使用以下代码使用交叉表创建数据透视表：
create table test_Crosstab( section text,
status text,
count numeric)
insert into test_Crosstab values ( 'A','Active',1)
,( 'A','Inactive',2)
,( 'B','Active',4)
,( 'B','Inactive',5)
select * from crosstab(
'select section
,status
,count
from test_crosstab'
)as ctab ("Section" text,"Active" numeric,"Inactive" numeric)
我设计了一种不同的动态方法，它使用动态记录类型（通过匿名过程构建的临时表）和 JSON。这对于无法安装 tablefunc/crosstab 扩展程序但仍然可以创建临时表或运行匿名过程的最终用户可能很有用。
示例假定所有 xtab 列都是相同类型（INTEGER），但列数是数据驱动和可变参数的。话虽如此，JSON 聚合函数确实允许混合数据类型，因此可以通过使用嵌入式复合（混合）类型来进行创新。
如果您想在 JSON 记录集函数中静态定义 rec. 类型（通过发出复合类型的嵌套 SELECTs），则其真正的核心可以缩减为一步。