Date: Fri, 29 Mar 2024 09:16:19 +0000 (UTC) Message-ID: <70031303.3735.1711703779404@docs.stiltsoft.com> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_3734_544153457.1711703779403" ------=_Part_3734_544153457.1711703779403 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
Switch the page to the edit mode.
Insert the Table Transformer macro and paste the tables or= the macros outputting tables within the macro body.
Select the macro and click Edit.
In the Presets tab select Merge t= ables preset and click Next.
Define the parameters if needed.
Save the macro and the page.
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 or= ders
In the SQL query tab in the macro browser you can find the default query= :
SELECT *=20 FROM T*
SELECT * outputs all columns from source tables. <= span>Replace the asterisk with a comma separated list of column labels in s= ingle quotes to output only these columns in the merged table:
SELECT 'Name', 'Age'=20 FROM T*
FROM T* gets data from all tables within the macro body= . Replace the asterisk with the number of the table to use the particular t= able only:
SELECT 'Name', 'Age'=20 FROM T1
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 Po= sition:
SELECT *=20 FROM T1=20 UNION SELECT *, 'Job' AS 'Position'=20 FROM T2
Switch the page to the edit mode.
Insert the Table Transformer macro and paste the tables or= the macros outputting tables within the macro body.
Select the macro and click Edit.
In the Presets tab select Lookup = tables preset.
Select the column for matching records and click Next= strong>.
Define the parameters if needed.
Save the macro and the page.
In the SQL query tab in the macro browser you can find the default query= :
SELECT *=20 FROM T1=20 OUTER JOIN T*=20 ON T1.'Name' =3D T*.'Name'
SELECT * outputs all columns from source tables.&n= bsp;Replace the asterisk with a comma separated list of column labels in si= ngle quotes to output only these columns in the merged table:
SELECT T1.'Name', T1.'Age', T2.'Certification'=20 FROM T1=20 OUTER JOIN T*=20 ON T1.'Name' =3D T*.'Name'
OUTER JOIN T* looks up all tables. You can also use oth= er functions:
ON T1.'Name' =3D T*.'Name' is the matching criteria. Th= e rows in the merged table should match on the column Name.
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'=20 FROM T1=20 JOIN T2=20 ON T1.'Id' =3D T2.'#'=20 JOIN T3=20 ON T1.'Id' =3D T3.'N'
=
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'=20 FROM T1=20 JOIN T2=20 ON T1.'Login' =3D T2.'Name'=20 JOIN T3=20 ON T2.'Level' =3D T3.'Level'
The following table settings are = available in the Table Transformer macro:
Decimal separator
Thousands separator
Decimal places
Transpose source tables
Transpose result tables
To set the settings:
Select the macro and click Edit.
Switch to the Settings tab.
Define settings.
Save the macro and the page.
The following view options are available in the Table Transformer macro:=
Show result as plain text can be enabled only if the result is display= ed as a table with a single cell.
To set the settings:
To get more options such as default sorting and row numbering, freezi= ng the first N rows/columns and others wrap the Table Transformer macro= in the Table Filte= r macro.