PostgreSQL crosstab/pivot problems

Question

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

I want to produce something like this:

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

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);

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?

score 3 · Accepted Answer · edited May 23 '17 at 10:32

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');

Query:

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);

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.

Edmon · Answer 2 · 2012-08-01T01:05:19.033

0

Instead of:

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);

try:

SELECT *
FROM crosstab('select pref_id::bigint, pref_name::text, pref_value::text
FROM prefs ORDER BY 1,2')
AS prefs (row_name bigint, carrier_1 text, carrier_2 text);

If you had:

    pref_id |  pref_name   |   pref_value
   ---------+--------------+----------------
   1 | PagerNumber  | 2125551234
   2 | PagerCarrier | @att.com
   3 | PagerCarrier | @something.com
   2 | PageNumber   | 2332323232
   3 | PagerCarrier | @somethingelse.com

You would have gotten:

    row_name |  carrier_1   |   carrier_2
   -----+--------------+----------------
   1 | 2125551234      |
   2 | @att.com        | 2332323232
   3 | @something.com  | @somethingelse.com

Postgress Crosstab Reference

edited Aug 01 '12 at 01:05

answered Aug 01 '12 at 00:12

Edmon

4,752
4
32
42

Thanks, but there was no difference in your solution and I get the same results. Where is the syntax error you're referring to? – nnyby Aug 01 '12 at 00:17
I am sorry I do not have access to Postgress to try it. Try adding order by like I did in the example. – Edmon Aug 01 '12 at 00:40
I see what is going on - you are getting a crosstab but it is not looking like what you expect. it is crosstabbing/pivoting pref id and value. Try to insert a bit more combinations of names and values. It should start showing a pivot. I think also your labels are confusing you. – Edmon Aug 01 '12 at 00:50
1

[Ain't no such thing as "Postgress"](http://wiki.postgresql.org/wiki/FAQ#What_is_PostgreSQL.3F_How_is_it_pronounced.3F_What_is_Postgres.3F). – Erwin Brandstetter Aug 01 '12 at 03:43

PostgreSQL crosstab/pivot problems

2 Answers2

Linked