postgresql - Postgres 交叉表混淆

Question

我正在努力从我的数据中实现以下目标:

docid,  yyyy,   tiOne,  tiTwo,  tiThree,  tiFour
d1      2011    txtA    txtB    txtC
d2      2012    txtD    txtE    txtF      txtG
d3      2013    txtH    txtI    txtJ
d4      2013    txtK

这是重新创建我的数据的方法:

CREATE TEMP TABLE t (
  docid   text
, yyyy    int
, timark  text
, txtmark text
);

INSERT INTO t VALUES 
  ('d1', 2011, 'tiOne', 'txtA'), 
  ('d1', 2011, 'tiTwo', 'txtB'), 
  ('d1', 2011, 'tiThree', 'txtC'), 
  ('d2', 2012, 'tiOne', 'txtD'),
  ('d2', 2012, 'tiTwo', 'txtE'),
  ('d2', 2012, 'tiThree', 'txtF'),
  ('d2', 2012, 'tiFour', 'txtG'),
  ('d3', 2013, 'tiOne', 'txtH'), 
  ('d3', 2013, 'tiTwo', 'txtI'), 
  ('d3', 2013, 'tiThree', 'txtJ'), 
  ('d4', 2013, 'tiOne', 'txtK')
;

这是我的代码

select *
FROM   crosstab(
      'SELECT docid, timark, txtmark
       FROM   t
       ORDER  BY 1,2')  -- needs to be "ORDER BY 1,2" here
AS ct ("docid" text, "timark" text, "txtmark" text);

但我得到的输出完全令人困惑，如下所示:

docid timark txtmark
d1    txtA   txtC
d2    txtG   txtD
d3    txtH   txtJ
d4    txtK   

'tiOne' 数据结构不佳，因此很难准确知道这些列中的内容，因此将这些值硬编码到代码中并不容易

Answer 1

交叉表查询别名应包含结果集的列名和类型。源查询的结果应按两列排序:行标识符 (docid) 和类别标识符 (timark)。不幸的是，类别名称的字母顺序不是预期的顺序。

在这种情况下，使用带有两个参数的crosstab 函数的形式。第二个参数是按预期顺序选择所有类别的查询。这种形式的交叉表还允许有额外的列(yyyy)并正确显示不完整的数据。

select *
FROM crosstab(
    $$ SELECT docid, yyyy, timark, txtmark
    FROM t
    ORDER BY 1 $$,
    $$ values ('tiOne'), ('tiTwo'), ('tiThree'), ('tiFour') $$)
AS ct ("docid" text, "yyyy" text, "tiOne" text, "tiTwo" text, "tiThree" text, "tiFour" text);

 docid | yyyy | tiOne | tiTwo | tiThree | tiFour 
-------+------+-------+-------+---------+--------
 d1    | 2011 | txtA  | txtB  | txtC    | 
 d2    | 2012 | txtD  | txtE  | txtF    | txtG
 d3    | 2013 | txtH  | txtI  | txtJ    | 
 d4    | 2013 | txtK  |       |         | 
(4 rows)