SELECT * FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text);
此示例生成类似于以下内容的集合
<== value columns ==>
row_name category_1 category_2
----------+------------+------------
row1 val1 val2
row2 val5 val6
FROM 子句必须定义输出为一个 row_name 列（与 SQL 查询的第一个结果列具有相同的数据类型），后跟 N 个 value 列（全部与 SQL 查询的第三个结果列具有相同的数据类型）。你可以设置任意数量的输出值列。输出列的名称由你决定。
crosstab 函数针对具有相同 row_name 值的每个连续输入行组生成一行输出。它使用这些行的 value 字段从左向右填充输出 value 列。如果组中的行数少于输出 value 列，则使用 null 值填充额外的输出列；如果行数多于该数量，则跳过额外的输入行。
在实际中，SQL 查询始终应该指定 ORDER BY 1,2 以确保输入行正确排序，即，具有相同 row_name 的值会聚集在一起并在行中正确排序。注意，crosstab 本身不会关注查询结果的第二列；它只是在那里供排序使用，以控制第三列的值在页面中显示的顺序。
这是一个完整示例
CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT);
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8');
SELECT *
FROM crosstab(
'select rowid, attribute, value
from ct
where attribute = ''att2'' or attribute = ''att3''
order by 1,2')
AS ct(row_name text, category_1 text, category_2 text, category_3 text);
row_name | category_1 | category_2 | category_3
----------+------------+------------+------------
test1 | val2 | val3 |
test2 | val6 | val7 |
(2 rows)
通过设置已将所需输出行类型连接到其定义中的自定义交叉表函数，您可以避免始终必须写出 FROM 从句来定义输出列。这在下一部分中有描述。另一种可能性是将必需的 FROM 从句嵌入到视图定义中。
另请参阅 \crosstabview 在 psql 中的命令，其提供类似于 crosstab() 的功能。
crosstabN(text sql)
crosstabN 函数是为通用 crosstab 函数设置自定义包装器的示例，这样您不必在调用 SELECT 查询时写出列名和类型。tablefunc 模块包含 crosstab2、crosstab3 和 crosstab4，其输出行类型定义为
CREATE TYPE tablefunc_crosstab_N AS (
row_name TEXT,
category_1 TEXT,
category_2 TEXT,
category_N TEXT
因此，当输入查询生成类型为 text 的 row_name 和 value 列，并且您需要 2、3 或 4 个输出值列时，可以直接使用这些函数。在其他所有方面，它们的行为与上面为通用 crosstab 函数描述的完全相同。
例如，上一部分中的示例也会起作用，如下所示
SELECT *
FROM crosstab3(
'select rowid, attribute, value
from ct
where attribute = ''att2'' or attribute = ''att3''
order by 1,2');
这些函数主要用于说明目的。您可以根据底层 crosstab() 函数创建自己的返回类型和函数。这样做有两种方法
contrib/tablefunc/tablefunc--1.0.sql 中类似示例中描述所需输出列的复合类型。然后定义一个唯一的函数名称，接受一个 文本 参数并返回 集合_类型名称，但链接到相同的底层 交叉表 C 函数。例如，如果源数据生成的行名称为 文本，值为 浮点数8，并且需要 5 个值列
CREATE TYPE my_crosstab_float8_5_cols AS (
my_row_name text,
my_category_1 float8,
my_category_2 float8,
my_category_3 float8,
my_category_4 float8,
my_category_5 float8
CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(text)
RETURNS setof my_crosstab_float8_5_cols
AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT;
使用 OUT 参数来隐式定义返回类型。相同的示例还可以通过这种方式完成
CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(
IN text,
OUT my_row_name text,
OUT my_category_1 float8,
OUT my_category_2 float8,
OUT my_category_3 float8,
OUT my_category_4 float8,
OUT my_category_5 float8)
RETURNS setof record
AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT;
crosstab(text source_sql, text category_sql)
crosstab 单参数形式的主要限制在于，它将组中的所有值视为相同，将每个值插入到第一个可用列中。如果需要将值列与特定数据类别相对应，并且某些组可能没有某些类别的相关数据，这种方法就不太适用。crosstab 双参数形式通过提供与输出列相对应的明确类别列表来处理这种情况。
source_sql 是生成源数据集的 SQL 语句。此语句必须返回一个 行名称 列、一个 类别 列和一个 值 列。它还可能有一个或多个 “其他” 列。必须将 row_name 列排在首位。必须将 category 列和 value 列按此顺序排在最后的两个位置。所有位于 row_name 和 category 之间的列都将被视为 “其他”。所有具有相同 row_name 值的行都应该采用相同的 “其他” 列。
例如，source_sql 可能生成类似如下内容的集合
SELECT row_name, extra_col, cat, value FROM foo ORDER BY 1;
row_name extra_col cat value
----------+------------+-----+---------
row1 extra1 cat1 val1
row1 extra1 cat2 val2
row1 extra1 cat4 val4
row2 extra2 cat1 val5
row2 extra2 cat2 val6
row2 extra2 cat3 val7
row2 extra2 cat4 val8
category_sql 是生成类别集合的 SQL 语句。此语句只能返回一列。它必须生成至少一行，否则将生成错误。此外，它不得生成重复值，否则会生成错误。category_sql 可能是类似以下内容
SELECT DISTINCT cat FROM foo ORDER BY 1;
-------
crosstab 函数被声明为返回 setof record，因此输出列的实际名称和类型必须在调用 SELECT 语句的 FROM 子句中定义，例如
SELECT * FROM crosstab('...', '...')
AS ct(row_name text, extra text, cat1 text, cat2 text, cat3 text, cat4 text);
这会产生类似以下内容的结果
<== value columns ==>
row_name extra cat1 cat2 cat3 cat4
---------+-------+------+------+------+------
row1 extra1 val1 val2 val4
row2 extra2 val5 val6 val7 val8
FROM 子句必须定义正确的数据类型及其对应数量的输出列。如果 source_sql 查询结果中包含 N 列，前 N-2 个列必须与前 N-2 个输出列相匹配。剩余输出列必须与 source_sql 查询结果最后一列类型相同，且其数量必须与 category_sql 查询结果中的行数完全一致。
crosstab 函数针对每一连续输入行组生成一行输出行，这些输入行的 row_name 值相同。输出 row_name 列以及任意 “额外” 列均从该组的第一行复制。输出 value 列填入匹配 category 值的行中的 value 字段。如果某行的 category 与 category_sql 查询的任何输出不匹配，则其 value 将被忽略。在该组中不存在匹配类别的输出列将填入 null 值。
实际上， source_sql 查询应始终指定 ORDER BY 1，以确保具有相同 row_name 的值被汇集到一起。但是，组内类别的排序并不重要。此外，必须确保 category_sql 查询输出的顺序与指定的输出列顺序相匹配。
下面是两个完整示例
create table sales(year int, month int, qty int);
insert into sales values(2007, 1, 1000);
insert into sales values(2007, 2, 1500);
insert into sales values(2007, 7, 500);
insert into sales values(2007, 11, 1500);
insert into sales values(2007, 12, 2000);
insert into sales values(2008, 1, 1000);
select * from crosstab(
'select year, month, qty from sales order by 1',
'select m from generate_series(1,12) m'
) as (
year int,
"Jan" int,
"Feb" int,
"Mar" int,
"Apr" int,
"May" int,
"Jun" int,
"Jul" int,
"Aug" int,
"Sep" int,
"Oct" int,
"Nov" int,
"Dec" int
year | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec
------+------+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------
2007 | 1000 | 1500 | | | | | 500 | | | | 1500 | 2000
2008 | 1000 | | | | | | | | | | |
(2 rows)
CREATE TABLE cth(rowid text, rowdt timestamp, attribute text, val text);
INSERT INTO cth VALUES('test1','01 March 2003','temperature','42');
INSERT INTO cth VALUES('test1','01 March 2003','test_result','PASS');
INSERT INTO cth VALUES('test1','01 March 2003','volts','2.6987');
INSERT INTO cth VALUES('test2','02 March 2003','temperature','53');
INSERT INTO cth VALUES('test2','02 March 2003','test_result','FAIL');
INSERT INTO cth VALUES('test2','02 March 2003','test_startdate','01 March 2003');
INSERT INTO cth VALUES('test2','02 March 2003','volts','3.1234');
SELECT * FROM crosstab
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
'SELECT DISTINCT attribute FROM cth ORDER BY 1'
rowid text,
rowdt timestamp,
temperature int4,
test_result text,
test_startdate timestamp,
volts float8
rowid | rowdt | temperature | test_result | test_startdate | volts
-------+--------------------------+-------------+-------------+--------------------------+--------
test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | | 2.6987
test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | Sat Mar 01 00:00:00 2003 | 3.1234
(2 rows)
您可以创建预定义函数以避免在每个查询中写出结果列的名称和类型。请参见前一部分的示例。此形式 crosstab 的底层 C 函数名为 crosstab_hash。
connectby(text relname, text keyid_fld, text parent_keyid_fld
[, text orderby_fld ], text start_with, int max_depth
[, text branch_delim ])
connectby 函数生成显示存储在表中的分层数据的显示。该表必须具有唯一标识行的键字段和引用每行的父代（如果有）的父键字段。 connectby 可以显示从任意行下降的子树。
表 F.32 说明了这些参数。
表 F.32. connectby 参数
键和父键字段可以是任何数据类型，但它们必须是同一类型。请注意，start_with 值必须以文本字符串形式输入，而无论键字段的类型是什么。
connectby 函数声明返回 setof record，因此实际的输出列名称和类型必须在调用 SELECT 语句的 FROM 子句中定义，例如
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
AS t(keyid text, parent_keyid text, level int, branch text, pos int);
前两列输出用于当前行的键及其父行的键；它们必须与表的键字段类型匹配。第三列输出是树中的深度，并且必须为 integer 类型。如果给出了 branch_delim 参数，则下一列输出是分支显示，并且必须为 text 类型。最后，如果给出了 orderby_fld 参数，则最后一列输出是一个序列号，并且必须为 integer 类型。
“branch” 输出列显示到达当前行的键所采用的路径。键由指定的 branch_delim 字符串分隔。如果不希望进行分支显示，则同时在输出列列表中省略 branch_delim 参数和分支列。
如果同一父级下的同级顺序很重要，则包括 orderby_fld 参数以指定按哪个字段对同级进行排序。此字段可以是任何可排序数据类型。如果且仅当指定了 orderby_fld 时，输出列列表中必须包含一个最终整数序列号列。
表示表和字段名称的参数按照原样复制到 connectby 在内部生成的 SQL 查询中。因此，如果名称包含混合大小写或特殊字符，则包括双引号。您可能还需要对表名称进行模式限定。
在大型表中，除非在父键字段上建立索引，否则性能很差。
重要的是，branch_delim 字符串不能出现在任何键值中，否则 connectby 可能会错误地报告无限递归错误。请注意，如果未提供 branch_delim，则使用默认值 ~ 来进行递归检测。
这里有一个示例
CREATE TABLE connectby_tree(keyid text, parent_keyid text, pos int);
INSERT INTO connectby_tree VALUES('row1',NULL, 0);
INSERT INTO connectby_tree VALUES('row2','row1', 0);
INSERT INTO connectby_tree VALUES('row3','row1', 0);
INSERT INTO connectby_tree VALUES('row4','row2', 1);
INSERT INTO connectby_tree VALUES('row5','row2', 0);
INSERT INTO connectby_tree VALUES('row6','row4', 0);
INSERT INTO connectby_tree VALUES('row7','row3', 0);
INSERT INTO connectby_tree VALUES('row8','row6', 0);
INSERT INTO connectby_tree VALUES('row9','row5', 0);
-- with branch, without orderby_fld (order of results is not guaranteed)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')
AS t(keyid text, parent_keyid text, level int, branch text);
keyid | parent_keyid | level | branch
-------+--------------+-------+---------------------
row2 | | 0 | row2
row4 | row2 | 1 | row2~row4
row6 | row4 | 2 | row2~row4~row6
row8 | row6 | 3 | row2~row4~row6~row8
row5 | row2 | 1 | row2~row5
row9 | row5 | 2 | row2~row5~row9
(6 rows)
-- without branch, without orderby_fld (order of results is not guaranteed)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)
AS t(keyid text, parent_keyid text, level int);
keyid | parent_keyid | level
-------+--------------+-------
row2 | | 0
row4 | row2 | 1
row6 | row4 | 2
row8 | row6 | 3
row5 | row2 | 1
row9 | row5 | 2
(6 rows)
-- with branch, with orderby_fld (notice that row5 comes before row4)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
AS t(keyid text, parent_keyid text, level int, branch text, pos int);
keyid | parent_keyid | level | branch | pos
-------+--------------+-------+---------------------+-----
row2 | | 0 | row2 | 1
row5 | row2 | 1 | row2~row5 | 2
row9 | row5 | 2 | row2~row5~row9 | 3
row4 | row2 | 1 | row2~row4 | 4
row6 | row4 | 2 | row2~row4~row6 | 5
row8 | row6 | 3 | row2~row4~row6~row8 | 6
(6 rows)
-- without branch, with orderby_fld (notice that row5 comes before row4)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0)
AS t(keyid text, parent_keyid text, level int, pos int);
keyid | parent_keyid | level | pos
-------+--------------+-------+-----
row2 | | 0 | 1
row5 | row2 | 1 | 2
row9 | row5 | 2 | 3
row4 | row2 | 1 | 4
row6 | row4 | 2 | 5
row8 | row6 | 3 | 6
(6 rows)
