0

I am pulling data and when I pull in the text field my results for the "distinct ID" are sometimes being duplicated when there are multiple results for that ID. Is there a way to concatenate the results into a single column/row rather than having them duplicated?

It looks like there are ways in other SQL platforms but I have not been able to find something that works in HANA.

Example

Select
     Distinct ID
From Table1

If I pull only Distinct ID I get the following:

ID
1
2
3
4

However when I pull the following:
Example

Select
     Distinct ID,Text
From Table1

I get something like

ID Text
1 Dog
2 Cat
2 Dog
3 Fish
4 Bird
4 Horse

I am trying to Concat the Text field when there is more than 1 row for each ID.

What I need the results to be (Having a "break" between results so that they are on separate lines would be even better but at least a "," would work):

ID Text
1 Dog
2 Cat,Dog
3 Fish
4 Bird,Horse
Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Jesse
  • 25
  • 1
  • 3
  • 10
  • 1
    using string_agg you can do this and link to show the answer is https://stackoverflow.com/questions/26673458/how-to-concatenate-multiple-rows-fields-in-a-sap-hana-table – Kiran Sep 13 '18 at 12:14

1 Answers1

0

I see Kiran has just referred to another valid answer in the comment, but in your example this would work. 

SELECT ID, STRING_AGG(Text, ',')
FROM TABLE1
GROUP BY ID;

You can replace the ',' with other characters, maybe a '\n' for a line break

I would caution against the approach to concatenate rows in this way, unless you know your data well. There is no effective limit to the rows and length of the string that you will generate, but HANA will have a limit on string length, so consider that.

Andy F
  • 91
  • 7