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

  • 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); – user11789512 Aug 15 at 3:36
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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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