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:

A column of values that remain unchanged, as row labels
A column of values that are pivoted, to become new column names
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:

MULTIPLE input columns that you would like to remain as-is in the output?
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!

SQL is not designed to do something like that. This is much better done in your front end code. Or maybe aggregate the dynamic columns into a single JSON value, — user330315
– user330315, Commented Sep 23, 2019 at 12:16

Fahmi · Accepted Answer · 2019-09-23 12:18:58Z

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

answered Sep 23, 2019 at 12:18

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Steve Perkins Over a year ago

Brilliant, thank you! I was so hung up on the term "pivot table" (which I'm not 100% sure this is when you have multiple "pivots"). I should have taken a step back and thought about ways to achieve the actual result, without getting hung up on terminology that steers toward a certain approach.

rd_nielsen · Accepted Answer · 2019-09-23 14:59:24Z

0

A different approach (that I have used) is to write the data out to a file, use a separate utility to crosstab it in the desired format, and import the result into a new table.

answered Sep 23, 2019 at 14:59

rd_nielsen

2,4592 gold badges14 silver badges19 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related