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