Date: Fri, 29 Mar 2024 05:28:33 +0000 (UTC) Message-ID: <212974489.3687.1711690113007@docs.stiltsoft.com> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_3686_1795565595.1711690113007" ------=_Part_3686_1795565595.1711690113007 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
The default SQL query works with no need to change it:
<=
img class=3D"confluence-embedded-image" draggable=3D"false" width=3D"468" c=
onfluence-query-params=3D"effects=3Dborder-simple,shadow-kn" src=3D"857ed28=
ff96b90690151cba4cd1b9a98" data-image-src=3D"/download/attachments/33359435=
/merged%20table.png?version=3D1&modificationDate=3D1553695000000&ap=
i=3Dv2" data-linked-resource-id=3D"33359448" data-linked-resource-version=
=3D"1" data-linked-resource-type=3D"attachment" data-linked-resource-defaul=
t-alias=3D"merged table.png" data-base-url=3D"https://docs.stiltsoft.com" d=
ata-linked-resource-content-type=3D"image/png" data-linked-resource-contain=
er-id=3D"33359435" data-linked-resource-container-version=3D"18" alt=3D"" h=
eight=3D"282">
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
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 macr= o:
To set the settings:
The following view options are available in the Table Transformer macro:=
To set the settings:
To get more options such as default sorting and row numbering, free= zing the first N rows/columns and others wrap the Table Transformer mac= ro in the Ta= ble Filter macro.