Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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: 

KeySummary
10Hello World
50Apple
33Cheese
10Hello World

     Expected Table: 

KeySummary
10Hello World
50Apple
33Cheese

 

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 !

1 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):

Thu 10-1.png

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

Thu 10-3.png

Thu 10-6.png

Thu 10-7.png

Hope this may help your case.

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

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

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

🏑 Atlympic Event: Confluence

Hello Community!  Quick disclaimer: We are running a contest on Community (The Atlympics!) from July 23rd - August 8th of 2021. If you are interested in participating in this contest (prizes! ...

464 views 18 16
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you