7

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.

Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
Nishanth Reddy
  • 589
  • 2
  • 14
  • 27

1 Answers1

19

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

How to Concatenate Column Value from Multiple Rows into a Single Column?

Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
  • 2
    Just what I was looking for. Trying to find little snippets like this in the Hana documentation is very painful. – arcynum Sep 07 '16 at 02:07
  • 1
    @Ankit I was looking for this information with coalesce kind of functions inside hana script reference but as it turned out, it was deep buried in aggregate functions! Thanks a lot for sharing this. – Tintin May 15 '17 at 23:36
  • @arcynum. What you said is so true! – Tintin May 15 '17 at 23:36