Creating temporary tables for reuse in SELECTS - Normalizing tables

Use case

You have a table with empty cells in some columns ('Unit 4' and 'Unit 5' for our example). You need to normalize the table: get rid of the empty cells and "move up" cells with data.

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:

   CREATE TABLE TU4;
   INSERT INTO TU4 
   SELECT T1.'Unit 4', ROWNUM(*) as '#' FROM T1 WHERE T1.'Unit 4' != 0;
   CREATE TABLE TU5;
   INSERT INTO TU5 
   SELECT T1.'Unit 5', ROWNUM(*) as '#' FROM T1 WHERE T1.'Unit 5' != 0;
   SELECT T1.'Record #', T1.'Unit 1', T1.'Unit 2', T1.'Unit 3', TU4.'Unit 4', TU5.'Unit 5' 
   FROM T1
   LEFT JOIN TU4 ON T1.'Record #' = TU4.'#'
   LEFT JOIN TU5 ON T1.'Record #' = TU5.'#'

   Copy

   SQL

6. Save the macro and the page.