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

[Raphael Chagnoleau]

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: 

Key	Summary
10	Hello World
50	Apple
33	Cheese
10	Hello World

     Expected Table: 

Key	Summary
10	Hello World
50	Apple
33	Cheese

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}]

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.

[Raphael Chagnoleau]

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}]

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*)