nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Table Transformer - concatenate data from multiple rows based on criteria

Joel Tabares

Hi,

SQL functions in Table Transformer is limited that i'm having trouble concatenating/merging data from multiple rows.

Table

Project | Remarks

P1 | The quick brown

P1 | Fox jumps

P2 | Over the

P2 | Lazy dog

I need it to produce the output below:

Project | Remarks

P1 | The quick brown Fox jumps

P2 | Over the Lazy dog

Accepted answers

Katerina Kovriga {Stiltsoft}

Hi @Joel Tabares ,

It seems that's a duplicate question for this one.

Let me repeat the answer here:

There are two options to resolve your case: the Table Transformer macro and the Pivot Table macro.

Option 1. Table Transformer

Use the following custom SQL query: SELECT T1.'Project', SUM(T1.'Remarks' + " ") AS 'Remarks' FROM T1 GROUP BY T1.'Project'

Option 2. Pivot Table

Recreate the settings from the screenshots below:

All comments

Bastian Stehmann

Hi Joel,

if you could add an ID to your source table, like this

ID | Project | Remarks

1 | P1 | The quick brown

2 | P1 | Fox jumps

3 | P2 | Over the

4 | P2 | Lazy dog

It works with this sql.

SELECT concat (a.'Remarks'," ", b.'Remarks') FROM T1 a, T1 b where a.'Project'=b.'Project' and a.'ID'<b.'ID'

Usually I would use the rownum instead of the ID in SQL, but that didn't work for me in the table transformer.

Andrey Khaneev _StiltSoft_

Hi @Bastian Stehmann ,

ROWNUM() function returns the current row number. You can try to use it instead of the ID column.

Katerina Kovriga {Stiltsoft}

Hi @Joel Tabares ,

It seems that's a duplicate question for this one.

Let me repeat the answer here:

There are two options to resolve your case: the Table Transformer macro and the Pivot Table macro.

Option 1. Table Transformer

Use the following custom SQL query: SELECT T1.'Project', SUM(T1.'Remarks' + " ") AS 'Remarks' FROM T1 GROUP BY T1.'Project'

Option 2. Pivot Table

Recreate the settings from the screenshots below:

Bastian Stehmann

Ah, great, thank you very much.

Joel Tabares

Thank you all. Yes it was a duplicate question. I accidentally created the first one under the product Jira and couldn't delete it so i created the same question under Confluence. I have what i need now. Thanks again!

Sofia Kargioti {QC Analytics}

Thanks @Katerina Kovriga _Stiltsoft_ it worked for me

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

cloud

confluence-cloud