PostgreSQL交叉表/枢轴问题 [英] PostgreSQL crosstab/pivot problems

问题描述

我有一个prefs表，这是相关的列:

I have a prefs table, and here are the relevant columns:

mydb=> SELECT pref_id, pref_name, pref_value FROM prefs;
 pref_id |  pref_name   |   pref_value
---------+--------------+----------------
       1 | PagerNumber  | 2125551234
       2 | PagerCarrier | @att.com
       3 | PagerCarrier | @something.com

我想产生这样的东西:

 section |  pager_number  | pager_carrier
---------+----------------+---------------
       1 | 2125551234     |
       2 |                | @att.com
       3 |                | @something.com

因此，我在下面的示例中使用了交叉表，用于stackoverflow: PostgreSQL交叉表查询

So I used crosstab, following this example on stackoverflow: PostgreSQL Crosstab Query

SELECT row_name AS section,
       category_1::text AS pager_number,
       category_2::text AS pager_carrier
FROM crosstab('select pref_id::bigint, pref_name::text, pref_value::text
    FROM prefs')
AS ct (row_name bigint, category_1 text, category_2 text);

所有值都进入pager_number，并且pager_carrier留空:

All the values are going into pager_number, and pager_carrier is left empty:

 section |  pager_number  | pager_carrier
---------+----------------+---------------
       1 | 2125551234     |
       2 | @att.com       |
       3 | @something.com |

任何人都可以看到发生了什么事吗?

Can anyone see what's going on?

推荐答案

测试用例(提供示例数据的首选方式):

Test case (preferred way to provide sample data):

CREATE TEMP TABLE prefs (pref_id int, pref_name text, pref_value text);

INSERT INTO prefs VALUES 
 (1, 'PagerNumber' , '2125551234')
,(2, 'PagerCarrier', '@att.com')
,(3, 'PagerCarrier', '@something.com');

查询:

SELECT *
FROM   crosstab(
       'SELECT pref_id, pref_name, pref_value
        FROM   prefs
        ORDER  BY 1, 2',

       $$VALUES ('PagerNumber'::text), ('PagerCarrier')$$
       )
AS x (section text, pager_number bigint, pager_carrier text);

精确返回问题中描述的结果.如果PagerNumber可以不是有效的bigint数字，则可以使用text.

Returns exactly the result depicted in your question. If a PagerNumber can be something else than a valid bigint number, use text instead.

您在问题中所指的答案已经过时，从一开始就永远不正确.我添加了正确的答案，并附有说明和链接.

The answer you are referring to in your question was outdated and never correct to begin with. I added a proper answer with explanation and links over there.

这篇关于PostgreSQL交叉表/枢轴问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！