How to pivot multiple columns in PostgreSQL 9.5?

Question

I have the sample data as follows and want to find if I can use crosstab function in this case.

Sample Data:

ITEM | CD | TYPE |PARTS | PART | CNT |
Item 1 | A | AVG |2 | 1 | 10 |
Item 1 | A | AVG |2 | 2 | 20 |
Item 1 | B | AVG |2 | 1 | 10 |
Item 1 | B | AVG |2 | 2 | 20 |
Item 1 | A | SUM |2 | 1 | 10 |
Item 1 | A | SUM |2 | 2 | 20 |
Item 1 | B | SUM |2 | 1 | 10 |
Item 1 | B | SUM |2 | 2 | 20 |

Expected Result:

ITEM | CD | AVG_1 | SUM_1 | AVG_2 | SUM_2 | 
Item 1 | A | 10 | 10 | 20 | 20 |
Item 1 | B | 10 | 10 | 20 | 20 |

In addition, if the parts are 3, then there will be an additional row for both AVG and SUM for A and B codes. This is dynamic and user can enter this value. How can multiple rows be flattened ?

In this requirement, Item 1 and code A has to display Part 1 Sum and Average and then Part 2 Sum and Average and this can change based on the value in Parts column.

Appreciate your help on this !!

• user11789512: I tried the following but receiving an error "ERROR: return and sql tuple descriptions are incompatible' CREATE TYPE i2 AS (a text, b numeric); SELECT item, cd ,(a1).a AS a1_type, (a1).b AS a1_cnt ,(a2).a AS a1_type, (a2).b AS a2_cnt ,(a3).a AS a1_type, (a3).b AS a3_cnt ,(a4).a AS a1_type, (a4).b AS a4_cnt FROM crosstab( 'SELECT item, cd, (type,cnt)::i2 FROM a ORDER BY item,cd' ) AS ct (item text, cd text,a1 i3,a2 i3,a3 i3,a4 i3)

Answer 1

Use the official (native) crosstab function from the tablefunc extension to perform pivots.

Although it has an initial learning curve, it will be better long term as it introduces less technical debt and less overengineered solutions to your organization.

Refer to the tablefunc official doc: https://www.postgresql.org/docs/current/tablefunc.html

Answer 2

The following query produced the results I require. However, if there is more than 2 parts (part of database column), then additional columns, AVG3, SUM3 need to be generated dynamically.

select a.ITEM, a.CD, a.AVG1, b.SUM1, a.AVG2, b.SUM2
from ( SELECT substr(row_name,1,POSITION('|' IN row_name)-1) as item
 substr(row_name,POSITION('|' IN row_name)+1,99) as cd,
 AVG1,
 AVG2
 FROM crosstab('SELECT item||''|''||cd, part::text, cnt::text 
 FROM a 
 WHERE type = ''AVG'' 
 order by item, cd, part',1) 
 AS ct (row_name text, AVG1 text, AVG2 text)
 ) a,
 ( SELECT substr(row_name,1,POSITION('|' IN row_name)-1) as name,
 substr(row_name,POSITION('|' IN row_name)+1,99) as cd,
 SUM1,
 SUM2
 FROM crosstab('SELECT item||''|''||cd, part::text, cnt::text 
 FROM a
 WHERE type = ''SUM'' 
 order by item, cd, part',1) 
 AS ct (row_name text, SUM1 text, SUM2 text)
 ) b
where a.item = b.item
 and a.cd = b.cd;

Answer 3

I prefer using filtered aggregation over the quite cumbersome (in my opinion) crosstab() function:

select item, 
 cd,
 avg(cnt) filter (where type = 'AVG' and part = 1) as avg_1,
 sum(cnt) filter (where type = 'SUM' and part = 1) as sum_1,
 avg(cnt) filter (where type = 'AVG' and part = 2) as avg_2,
 sum(cnt) filter (where type = 'SUM' and part = 2) as sum_2
from the_table
group by item, cd
order by item, cd 

This also makes adding new columns easier I think.