Table Transformer macro - merging 2 tables by multiple values

Emil December 21, 2022

Hi,

I would like to merge 2 tables with the Table Transformer macro, but in the first table (Issues) there are multiple values (names and PIDs) in the Names field that I would like to use to merge it with the second table (Persons). I would like to merge the tables by PID. Here is an example in the picture.

 

image.png

 

So, my question is what would be the SQL for that. How can I get the PIDs from the Names field easily? And is it possible to merge these tables in one step?

 

Thanks,

Emil

1 answer

3 votes
Katerina Rudkovskaya _Stiltsoft_
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
December 21, 2022

Hi @[deleted],

I can suggest the following structure for your case:

Wed 4-1.png

Here is the SQL query for your Table Transformer macro:

SELECT * FROM T1
LEFT JOIN T2 ON T1.'Names'->indexOf(T2.'PID'+")") > -1

Wed 4-2.png

To group the result table in a more easy-to-read way, you may use the Pivot Table macro:

Wed 4-3.png

Hope it helps your case.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events