0

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

1
  • 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); Aug 15, 2019 at 3:36

3 Answers 3

0

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

0

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;
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.