How to pivot or 'merge' rows with column names?

Question

I have the following table:

crit_id   | criterium  | val1  | val2
----------+------------+-------+--------
  1       |     T01    |   9   |   9
  2       |     T02    |   3   |   5
  3       |     T03    |   4   |   9
  4       |     T01    |   2   |   3
  5       |     T02    |   5   |   1
  6       |     T03    |   6   |   1

I need to convert the values in 'criterium' into columns as 'cross product' with val1 and val2. So the result has to lool like:

T01_val1 |T01_val2 |T02_val1 |T02_val2 | T03_val1 | T03_val2
---------+---------+---------+---------+----------+---------
   9     |   9     |    3    |    5    |    4     |    9
   2     |   3     |    5    |    1    |    6     |    1

Or to say differently: I need every value for all criteria to be in one row.

This is my current approach:

select 
   case when criterium = 'T01' then val1 else null end as T01_val1,
   case when criterium = 'T01' then val2 else null end as T01_val2,
   case when criterium = 'T02' then val1 else null end as T02_val1,
   case when criterium = 'T02' then val2 else null end as T02_val2,
   case when criterium = 'T03' then val1 else null end as T03_val1,
   case when criterium = 'T03' then val2 else null end as T04_val2,
from crit_table;

But the result looks not how I want it to look like:

T01_val1 |T01_val2 |T02_val1 |T02_val2 | T03_val1 | T03_val2
---------+---------+---------+---------+----------+---------
   9     |   9     |  null   |  null   |  null    |  null
   null  |  null   |    3    |    5    |  null    |  null
   null  |  null   |  null   |  null   |   4      |    9

What's the fastest way to achieve my goal?

Bonus question:

I have 77 criteria and seven different kinds of values for every criterium. So I have to write 539 case statements. Whats the best way to create them dynamically?

I'm working with PostgreSql 9.4

Answer 1

Prepare for crosstab

In order to use crosstab() function, the data must be reorganized. You need a dataset with three columns (row number, criterium, value). To have all values in one column you must unpivot two last columns, changing at the same time the names of criteria. As a row number you can use rank() function over partitions by new criteria.

select rank() over (partition by criterium order by crit_id), criterium, val
from (
    select crit_id, criterium || '_v1' criterium, val1 val
    from crit
    union
    select crit_id, criterium || '_v2' criterium, val2 val
    from crit
    ) sub
order by 1, 2

 rank | criterium | val 
------+-----------+-----
    1 | T01_v1    |   9
    1 | T01_v2    |   9
    1 | T02_v1    |   3
    1 | T02_v2    |   5
    1 | T03_v1    |   4
    1 | T03_v2    |   9
    2 | T01_v1    |   2
    2 | T01_v2    |   3
    2 | T02_v1    |   5
    2 | T02_v2    |   1
    2 | T03_v1    |   6
    2 | T03_v2    |   1
(12 rows)

This dataset can be used in crosstab():

create extension if not exists tablefunc;

select * from crosstab($ct$
    select rank() over (partition by criterium order by crit_id), criterium, val
    from (
        select crit_id, criterium || '_v1' criterium, val1 val
        from crit
        union
        select crit_id, criterium || '_v2' criterium, val2 val
        from crit
        ) sub
    order by 1, 2
    $ct$)
as ct (rank bigint, "T01_v1" int, "T01_v2" int, 
                    "T02_v1" int, "T02_v2" int, 
                    "T03_v1" int, "T03_v2" int);

 rank | T01_v1 | T01_v2 | T02_v1 | T02_v2 | T03_v1 | T03_v2
------+--------+--------+--------+--------+--------+--------
    1 |      9 |      9 |      3 |      5 |      4 |      9
    2 |      2 |      3 |      5 |      1 |      6 |      1
(2 rows)

Alternative solution

For 77 criteria * 7 parameters the above query may be troublesome. If you can accept a bit different way of presenting the data, the issue becomes much easier.

select * from crosstab($ct$
    select 
        rank() over (partition by criterium order by crit_id),
        criterium,
        concat_ws(' | ', val1, val2) vals
    from crit
    order by 1, 2
    $ct$)
as ct (rank bigint, "T01" text, "T02" text, "T03" text);

 rank |  T01  |  T02  |  T03
------+-------+-------+-------
    1 | 9 | 9 | 3 | 5 | 4 | 9
    2 | 2 | 3 | 5 | 1 | 6 | 1
(2 rows)