Read more:

Merge Tables preset

Selecting preset

  1. Switch the page to the edit mode.

  2. Insert the Table Transformer macro and paste the tables or the macros outputting tables within the macro body.

  3. Select the macro and click Edit.

  4. In the Presets tab select Merge tables preset and click Next.

  5. Define the parameters if needed.

  6. Save the macro and the page.

The Merge tables preset merges tables with the same column labels (headers) into a combined report (additional rows are added).

The default SQL query works with no need to change it:

  • When the columns have the same column labels (look here to learn what query to use when the column labels differ)

  • When the columns are both in the same or in the different orders

Default SQL query and custom cases (optional)

SQL query explanation

In the SQL query tab in the macro browser you can find the default query:

SELECT * 
FROM T*
SQL

SELECT * outputs all columns from source tables. Replace the asterisk with a comma separated list of column labels in single quotes to output only these columns in the merged table:

SELECT 'Name', 'Age' 
FROM T*
SQL

FROM T* gets data from all tables within the macro body. Replace the asterisk with the number of the table to use the particular table only:

SELECT 'Name', 'Age' 
FROM T1
SQL

SQL query when the column labels differ

If you want to merge tables with different column labels, use this query example that outputs the column Job from the second table as the column Position:

SELECT * 
FROM T1 
UNION
SELECT *, 'Job' AS 'Position' 
FROM T2
SQL

Lookup Tables preset

Selecting preset

  1. Switch the page to the edit mode.

  2. Insert the Table Transformer macro and paste the tables or the macros outputting tables within the macro body.

  3. Select the macro and click Edit.

  4. In the Presets tab select Lookup tables preset.

  5. Select the column for matching records and click Next.

  6. Define the parameters if needed.

  7. Save the macro and the page.

The Lookup tables preset joins tables into a combined report (additional columns are added) using a unique key column.

Default SQL query and custom cases (optional)

SQL query explanation

In the SQL query tab in the macro browser you can find the default query:

SELECT * 
FROM T1 
OUTER JOIN T* ON T1.'Name' = T*.'Name'
SQL

SELECT * outputs all columns from source tables. Replace the asterisk with a comma separated list of column labels in single quotes to output only these columns in the merged table:

SELECT 
	T1.'Name', 
	T1.'Age', 
	T2.'Certification' 
FROM T1 
OUTER JOIN T* ON T1.'Name' = T*.'Name'
SQL

OUTER JOIN T* looks up all tables. You can also use other functions:

  • OUTER JOIN outputs all rows from all tables even if there are no matches.
  • JOIN outputs only those rows, that have matches.
  • LEFT JOIN outputs all rows from the first table even if there are no matches.
  • RIGHT JOIN outputs all rows from the second table even if there are no matches.

ON T1.'Name' = T*.'Name' is the matching criteria. The rows in the merged table should match on the column Name.

SQL query when the column labels differ

If you want to join tables with different labels of columns for matching, use this query example.  The rows in the joint table should match by the column Id for Table 1, # for Table 2, N for Table 3:

SELECT
   T1.'Id',
   T1.'Full name',
   T2.'Login',
   T3.'Personal space',
   T3.'Create space',
   T3.'Confluence admin',
   T3.'System admin' 
FROM T1 
JOIN T2 ON T1.'Id' = T2.'#' 
JOIN T3 ON T1.'Id' = T3.'N'
SQL

SQL query when joining tables with chain look up

If you want to merge two tables by one criterion and the third table by another criterion, use this query. Table 1 matches Table 2 by Login or name, then Table 3 matches them by Level:

SELECT
   T1.'Full name',
   T2.'Level',
   T3.'Personal space',
   T3.'Create space',
   T3.'Confluence admin',
   T3.'System admin' 
FROM T1 
JOIN T2 ON T1.'Login' = T2.'Name' 
JOIN T3 ON T2.'Level' = T3.'Level'
SQL

Managing parameters of the macro

Table settings

The following table settings are available in the Table Transformer macro:

To set the settings:

  1. Select the macro and click Edit.

  2. Switch to the Settings tab.

  3. Define settings.

  4. Save the macro and the page.

View options

The following view options are available in the Table Transformer macro:

  • Show/Hide source data
  • Show result as plain text
  • Export to CSV button
  • Export to PDF button
  • Export to Word button

Show result as plain text can be enabled only if the result is displayed as a table with a single cell.

To set the settings:

  1. Select the macro and click Edit.
  2. Switch to the Options tab.
  3. Define settings.
  4. Save the macro and the page.

To get more options such as default sorting and row numbering, freezing the first N rows/columns and others wrap the Table Transformer macro in the Table Filter macro.