Skip to Content
avatar image
Former Member

How to convert a column into comma-separated string in HANA DB

In SQL server we could do something like this. Please see the source here.

declare @results varchar(500)
select @results = coalesce(@results + ',', '') +  convert(varchar(12),col)
from t
order by col
select @results as results
| RESULTS |
-----------
| 1,3,5,9 |

I need do this in order to use it inside an inner query of a join.

e.g. on something like this.

SELECT * FROM Table1 join Table2 on Table1.col1 = (select converToCommaSeparatedFn(Table2.col2) );; -- Table1. col1 contains comma separated id's.

How do I do it in HANA DB?

I am even Ok to create a Table UDF for this function - but I think the table UDF will only return the table output and can't be used in inner queries of the join condition?

I didn't find much literature on Table UDFs or TPV UDF's.

Thanks

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jul 11, 2017 at 07:40 PM

    A simple look into the online documentation or searching here in the forum would produce the STRING_AGG function as result. This function does exactly what you require, when you use a comma as separator sign.

    Regards,
    Florian

    Add comment
    10|10000 characters needed characters exceeded