Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,293,785
Community Members
 
Community Events
165
Community Groups

Table Transformer - Merge Cells with Duplicate Rows

Edited

Hello Community!

I would like to take the following information:

Header 1Header 2Header 3
FruitLetter AApple
FruitLetter AApricot
FruitLetter BBanana
FruitLetter BBlueberry
FruitLetter CCherry

And transform it into the following:

*Updated 6-May-2021 - Original post unable to process merged cells.  Removed original content and replaced with image of desired output.

Snag_2879231c.png

Thank you for your insight!
Emily

1 answer

Hello Emily,

Here is the SQL:

SELECT T1.'Header 1', T1.'Header 2', 
FORMATWIKI(SUM(T1.'Header 3' + "\n")) AS 'Header 3'
FROM T1
GROUP BY T1.'Header 1', T1.'Header 2'

 And the result:

2021-05-07_19h31_05.png

Or you can use the Pivot Table macro (it is also shipped with the Table Filter and Charts app):

2021-05-07_19h32_11.png

Don't forget to hide totals:

2021-05-07_19h34_14.png

Is there a way to use SQL but have each item after the merged column appear in it's own row with a border still around it? I know the "\n" creates a line break, but it is still making my table very messy. I would prefer them to have their own row. 

Hi @Madison Small ,

The Table Transformer macro sees the source table as a mini SQL database, so it can merge cells into one big cell by joining their contents. If we leave a border, then they will be counted as two separate cells and you won't get an aggregated table at all. 

Maybe it will be easier to read your table when using ", " instead of the "\n": you'll get "Apple, Apricot" as a result.

Like # people like this

@Katerina Kovriga _Stiltsoft_ How would you do something like this with labels? When I try to merge cells that container labels, then labels are turned into plain strings and lose their formatting.

Try to use the Pivot Table macro instead of the Table Transformer - the initial formatting of the merged data will be preserved.

Maybe this is not as similar as I thought. So I would like to keep the formatting of my result table which would be a vertical header column with the data in a column to the right of the headers. I am merging 4 tables together and need to get just the column that contains labels to merge together. Unfortunately, the pivot table manipulates the result table too much and also does a lot of header renaming.

Desired Result:

header 1 | data

header 2 | Label1, label2, label 3, etc (These can be on newlines too)

 

Originals:

Table 1

header 1   | header 2

---------   ---------

Data.           label 1, label 2

 

Table 2

header 1   | header 2

---------   ---------

Data.           label, 2, label 3

 

etc

If you merge labels into one cell, then the Table Transformer macro may lose the original formatting because it is a new cell with simple text strings for it.

We have similar ticket in our backlog and added your vote to it, when the feature is implemented, we'll get back to the thread.

Suggest an answer

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

Confluence: Where work and wellness meet

Feeling overwhelmed by the demands of work and life? With a 25% increase in the prevalence of anxiety and depression worldwide during the pandemic, for most of us, it’s a resounding yes . 🙋‍♀️ ...

765 views 5 21
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