How to concatenate multiple rows' fields in a sap hana table

sql hana

Nishanth Reddy · Oct 31, 2014 · Viewed 22k times · Source

I use SAP-HANA database. I have a simple 2 column table whose columns are id, name. The rows are these:

1 - tom
1 - harry
1 - jack
2 - larry

I would like to group the rows by the id, and concatenate the names into a field, and thus obtain this:

1 - tom, harry, jack
2 - larry

Can you please tell me how we can perform this operation in sap-hana? Thanks in advance.

Answer

If you are using HANA with revision 70, you can try this:-

SELECT ID, STRING_AGG(Name, ',')  AS Names
FROM TAB
GROUP BY ID;

And for more info read this