0

I am trying to use crosstab() to generate a tricky pivot which is not really a true pivot. I am looking to get the key names as column names rather than the category.

E.g.

create table pivot (id serial, key text, category text, value text);

insert into pivot (key, category, value) values
    ('key_1', 'theme_1', 'a'),
    ('key_1', 'theme_2', 'b'),
    ('key_2', 'theme_1', 'aa'),
    ('key_2', 'theme_2', 'bb'),
    ('key_3', 'theme_1', 'aaa'),
    ('key_3', 'theme_2', 'bbb');

select * from pivot;
 id |  key  | category | value
----+-------+----------+-------
  1 | key_1 | theme_1  | a
  2 | key_1 | theme_2  | b
  3 | key_2 | theme_1  | aa
  4 | key_2 | theme_2  | bb
  5 | key_3 | theme_1  | aaa
  6 | key_3 | theme_2  | bbb
(6 rows)

select crosstabWTF(
    $q1$select crosstab2(
        $q2$select key, category, value from pivot$q2$
    )$q1$);
 key_1 | key_2 | key_3 | category
-------+-------+-------+----------
 a     | aa    | aaa   | theme_1
 b     | bb    | bbb   | theme_2

Also hopefully able to account for any number of different category values, not just 2.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.