Merging Jira Issue tables by linked issues and keys

Use Case

You need to merge two Jira Issue tables by linked issues (a comma separated list) from one table and issue keys from another.

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 T1.'T',T1.'Key',T1.'Summary',
   CONCAT_VIEW_AGGR(FORMATWIKI(T2.'T' , " \n")) AS 'Type - linked issues',
   CONCAT_VIEW_AGGR(FORMATWIKI(T2.'Key', " \n")) AS 'Key - linked issues',
   CONCAT_VIEW_AGGR(FORMATWIKI(T2.'Summary', " \n")) AS 'Summary - linked issues',
   CONCAT_VIEW_AGGR(FORMATWIKI(T2.'Status', " \n")) AS 'Status - linked issues'
   FROM T1 LEFT JOIN T2 ON T1.'Linked Issues'->split(" , ")->indexOf(T2.'Key'::string) > -1
   GROUP BY T1.'Key', T1.'T',T1.'Summary'

   SQL

6. Click Next. 

7. Define the date format .

8. Save the macro and the page.
   
   

A Javascript method in joing tables is used for splitting a comma-separated list of linked issues.

The CONCAT_VIEW_AGGR funсtion is used for concatenation and aggregation table data preserving their original formatting.

The FORMATWIKI funсtion is used for adding line breaks.

The GROUP BY statement groups rows that have the same values into summary rows.