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

[former_member244474]

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 |

How do I do it in HANA DB?

[pfefferf · Accepted solution]

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

[former_member244474 · Accepted solution]

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

[former_member244474 · Accepted solution]

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

[lbreddemann · Accepted solution]

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.