CODEWITHSUNDEEP
postgresqlcrosstab
belfastcowboy24
belfastcowboy24

Reputation: 317

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

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.

Upvotes: 0

Views: 148

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 2

Related Questions