Skip to Content
0

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

Apr 04, 2017 at 04:58 AM

339

avatar image
Former Member

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Florian Pfeffer
Jul 11, 2017 at 07:40 PM
0

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

Show 3 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Yeah, I had found it too. Searching in the literature was not very obvious or easy though.

I am sorry, I should have updated my answer.

Thanks

0
Former Member

Hi Florian,

Is there an easy to do the reverse as well? Converting the comma (delimited separated) values into a column? Or do I have to write my own function for that?

Thanks

0

Yes, for that you need a custom function. Fortunately, you're not the first to ask this question and you can put your google-foo to find examples for this in the archive pages of SCN.

1