PostgreSQL: Transforming rows into columns when more than three columns are needed

Question

I have a table like the following one:

+---------+-------+-------+-------------+--+
| Section | Group | Level | Fulfillment |  |
+---------+-------+-------+-------------+--+
| A       | Y     |     1 | 82.2        |  |
| A       | Y     |     2 | 23.2        |  |
| A       | M     |     1 | 81.1        |  |
| A       | M     |     2 | 28.2        |  |
| B       | Y     |     1 | 89.1        |  |
| B       | Y     |     2 | 58.2        |  |
| B       | M     |     1 | 32.5        |  |
| B       | M     |     2 | 21.4        |  |
+---------+-------+-------+-------------+--+

And this would be my desired output:

+---------+-------+--------------------+--------------------+
| Section | Group | Level1_Fulfillment | Level2_Fulfillment |
+---------+-------+--------------------+--------------------+
| A       | Y     | 82.2               | 23.2               |
| A       | M     | 81.1               | 28.2               |
| B       | Y     | 89.1               | 58.2               |
| B       | M     | 32.5               | 21.4               |
+---------+-------+--------------------+--------------------+

Thus, for each section and group I'd like to obtain their percents of fulfillment for level 1 and level 2. To achieve this, I've tried crosstab(), but using this function returns me an error ("The provided SQL must return 3 columns: rowid, category, and values.") because I'm using more than three columns (I need to maintain section and group as identifiers for each row). Is possible to use crosstab in this case?

Regards.

user330315 · Accepted Answer · 2020-10-27 10:24:23Z

2

I find crosstab() unnecessary complicated to use and prefer conditional aggregation:

select section, 
       "group", 
       max(fulfillment) filter (where level = 1) as level_1,
       max(fulfillment) filter (where level = 2) as level_2
from the_table
group by section, "group"
order by section;

Online example

answered Oct 27, 2020 at 10:24

user330315

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

1 Comment

belfastcowboy24 Over a year ago

You are completely right, @a_horse_with_no_name. Using crosstab is unnecesarily complex to achieve my result. Your solution works like a charm and is easier to understand.

Collectives™ on Stack Overflow

PostgreSQL: Transforming rows into columns when more than three columns are needed

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related