How to create a PostgreSQL pivot table that pivots multiple columns?

Question

I have been been exploring the crosstab() function in PostgreSQL's tablefunc extension module, as a way of producing pivot tables.

It's great, but seems suitable for only the most basic of use cases. It generally supports only THREE columns of input:

1. A column of values that remain unchanged, as row labels
2. A column of values that are pivoted, to become new column names
3. A column of values that become the values for their respective new pivot columns

Basically taking this:

+------+----------+-------+
| ITEM |  STATUS  | COUNT |
+------+----------+-------+
| foo  | active   |    12 |
| foo  | inactive |    17 |
| bar  | active   |    20 |
| bar  | inactive |     4 |
+------+----------+-------+

... and producing this:

+------+--------+--------+----------+
| ITEM | STATUS | ACTIVE | INACTIVE |
+------+--------+--------+----------+
| foo  | active |     12 |       17 |
| bar  | active |     20 |        4 |
+------+--------+--------+----------+

But what about more complex use cases? What if you have:

1. MULTIPLE input columns that you would like to remain as-is in the output?
2. MULTIPLE input columns that you would like to pivot into new columns?

As in the example below:

+--------+-----------------+---------+--------+-------+------------------+
| SYSTEM |  MICROSERVICE   |  MONTH  | METRIC | VALUE | CONFIDENCE_LEVEL |
+--------+-----------------+---------+--------+-------+------------------+
| batch  | batch-processor | 2019-01 | uptime |    99 |                2 |
| batch  | batch-processor | 2019-01 | lag    |    20 |                1 |
| batch  | batch-processor | 2019-02 | uptime |    97 |                2 |
| batch  | batch-processor | 2019-02 | lag    |    35 |                2 |
+--------+-----------------+---------+--------+-------+------------------+

Where the first THREE columns should carry over as-is for each row (no grouping or aggregation). And the metric column has TWO associated columns (i.e. value and confidence_level) to pivot for it?

+--------+-----------------+---------+--------------+-------------------+-----------+----------------+
| SYSTEM |  MICROSERVICE   |  MONTH  | UPTIME_VALUE | UPTIME_CONFIDENCE | LAG_VALUE | LAG_CONFIDENCE |
+--------+-----------------+---------+--------------+-------------------+-----------+----------------+
| batch  | batch-processor | 2019-01 |           99 |                 2 |        20 |              1 |
| batch  | batch-processor | 2019-02 |           97 |                 2 |        35 |              2 |
+--------+-----------------+---------+--------------+-------------------+-----------+----------------+

I'm not sure if this still fits the strict definition of "pivot table". But is such a result possible with crosstab(), or any other readily-available PostgreSQL function? If not, then how could it be produced with a custom PL/pgSQL function? Thanks!

Answer 1

You can try using conditonal aggregation

select system,MICROSERVICE , MONTH,
max(case when METRIC='uptime' then VALUE end) as uptime_value,
max(case when METRIC='uptime' then CONFIDENCE_LEVEL end) as uptime_confidence,
max(case when METRIC='lag' then VALUE end) as lag_value,
max(case when METRIC='lag' then CONFIDENCE_LEVEL end) as lag_confidence
from tablename
group by system,MICROSERVICE , MONTH