How to delete duplicates rows to keep only one in Transformer table (Confluence) ?

Question

Afternoon everyone,

Here is my use case - I have multiple tables with hundreds of rows each and I am using the Transformer macro to merge them all into one.

Problem with that: few rows are duplicated (all data in each columns are identical).

My question is: What SQL query should I use to delete (or hide) duplicated rows to only keep one in the table ?

Example:

Initial Table:

KeySummary10Hello World50Apple33Cheese10Hello World

Expected Table:

KeySummary10Hello World50Apple33Cheese

Partial solution: I have encountered a question previously asked on that matter which uses below SQL query, but all rows are deleted:

SELECT DISTINCT * FROM T1

Thanks for your help, and have a great summer !

Katerina Kovriga {Stiltsoft} · Answer

I've changed the original answer (not sure what version you have seen), so you may try the option with one Table Transformer and the following SQL query:

SELECT DISTINCT * FROM
(SELECT * FROM T*)

Raphael Chagnoleau · Answer

Katerina, thank you so much for your help - I used your second solution (create 2 Transformer tables) and it works perfectly !

As my table had a couple of Columns (10 or so), I displayed all columns name into the SQL query to make them appear in the list.

Once again, thanks a lot, you saved me a lot of time and brain cells !

Have a great day,

Raphaël

Katerina Kovriga {Stiltsoft} · Answer

Hi @Raphael Chagnoleau ,

At first I want to mention our new feature regarding the Table Excerpt/Table Excerpt Include macros: if your tables are manually created, I assume that you reuse them with the help of the Table Excerpt macros and collect them with the help of the Table Excerpt Include macro.

If this is the case, then you don't need the Table Transformer macro to merge your tables into one big report - just tick the corresponding checkbox into the Table Excerpt Include macro settings (and you may show additional page meta data as well):

If your tables are based on the Jira Issues macros, then you may use the Table Transformer macro to combine them with the help of the Merge preset (as, I suppose, your are doing now).

The easiest way to get rid of the duplicate rows is to go to the Custom transformation and use the following SQL query:

SELECT DISTINCT * FROM
(SELECT * FROM T*)

Hope this may help your case.