sqlspagobi

How to concatenate text from multiple rows based on another common field in BI?

I need to concat the data in column B into a single line grouped by column A. I am using a Spago BI UI that limits me to distinct clause, group by, calculated values, where and having clauses. Wondering if anyone has any ideas.

a b  
1 Text
1 Text1
2 Text2
2 Text3
2 Text4

Results being:

a b
1 text, text1
2 text2, text3, text4
Solution

  • In SQL SERVER 2017 + / Postgres:

    select a , STRING_AGG(b, ',')
from table 
group by a

    In MYSQL:

    select a , GROUP_CONCAT(b, ',')
from table 
group by a