Aggregating by headers

Use case

You are to aggregate headers from the Dev-stage 1 to Dev-stage 5 columns by their values and products.

Solution

1. Switch the page to the edit mode.
2. Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.
3. Select the macro and click Edit.
4. In the Presets tab select Custom transformation and click Next.

5. Enter the following SQL query:

   SELECT 'Product', 'Value', ARRAY('Dev-stage') AS 'Dev-stage'
    FROM ( SELECT * FROM T1 UNPIVOT ('Value' for 'Dev-stage' 
   in (T1.'Dev-stage 1',T1.'Dev-stage 2',T1.'Dev-stage 3',T1.'Dev-stage 4',T1.'Dev-stage 5')))
    GROUP BY 'Product', 'Value'

6. Save the macro. 
7. Insert another Table Transformer and wrap the previously created layout with the macro.
8. Select the macro and click Edit.
9. In the Presets tab select Custom transformation and click Next.

10. Enter the following SQL query:

    SELECT * FROM T1
    PIVOT (FIRST ('Dev-stage') for 'Value')

11. Save the macro and the page.