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 !!

Answer 1

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 2

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.