crosstab with 2 (or more) row names

Question

I'm trying to transpose a table which has 2 row names.
Postgres documentation mentions the crosstab() function being able to only handle 1 row name, but I have 2 row names, like first name and last name.

my intital table is:

fn |  ln |  file_type |attribute
--------------------------------
A  |  1  |  cat1     |abc
A  |  2  |  cat1     |gth
A  |  1  |  cat2     |fgh
B  |  1  |  cat2     |gth

and I want my final table to be with 2 initial rows and the file_type transposed

 fn |  ln |  cat1 | cat2
--------------------------------
 A  |   1 |  abc  | fgh
 A  |   2 |  gth  | 
 B  |   1 |       | gth

I have not been able to find what I need in functools ...

version is Postgres 9.3

Erwin Brandstetter · Answer 1 · 2018-02-08T16:41:24.873

4

Use crosstab() from the additional module tablefunc.

The specific difficulty here is that the "row name" consists of two columns. I concatenate for the purpose of the query and do not display the concatenated column at the end.
Assuming fn and ln are NOT NULL. Untested:

SELECT fn, ln, cat1, cat2
FROM crosstab(
       'SELECT fn || ln AS row_name, fn, ln, file_type, attribute
        FROM   t
        ORDER  BY fn, ln, file_type'

      ,$$VALUES ('cat1'::text), ('cat2')$$)
AS t (row_name text, fn text, ln int, cat1 text, cat2 text);

Another option would be to add a surrogate "row name" with a window function like dense_rank() and treat the defining two columns as "extra columns". Example:

Postgresql crosstab query with multiple "row name" columns

Basics:

PostgreSQL Crosstab Query

edited Feb 08 '18 at 16:41

answered Jan 15 '14 at 19:05

Erwin Brandstetter

605,456
145
1,078
1,228

this is great solution if the extra cols are "expected to be the same" for *all* row_name. Is there a way to use crosstab for multilevel pivoting / hierarchical pivoting? – zam6ak Feb 08 '17 at 16:23
1

Ha! Found a solution in one of your [other answers](http://stackoverflow.com/a/32918155/481904). Except I used dense_rank() to generate surrogate key over groupings I wanted and then used it as row_name to crosstab. – zam6ak Feb 08 '17 at 16:49

score 1 · Answer 2 · answered Jan 15 '14 at 16:41

This is my typical fallback approach when pivoting in the database:

select fn,
       ln,
       max(case file_type when 'cat1' then attribute end),
       max(case file_type when 'cat2' then attribute end)
from testdata
group by fn,
         ln
;

If you remove the aggregates then you can see that the case expressions will be non-null only for the class's row with the specified category (and all other rows should be null for that class in that category, although this will silently just pick one if that's not the case). The max is just one way to pick the non-null value (you could use min, or any other aggregate that will pick a single non-null value out of a set).

crosstab with 2 (or more) row names

2 Answers2

Linked