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

[Katerina Kovriga {Stiltsoft} · Accepted solution]

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:

[Sofia Kargioti _QC Analytics_]

Thanks @Katerina Kovriga {Stiltsoft}  it worked for me

[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.

[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!