Skip to Content
0
Former Member
Jan 07, 2014 at 05:04 PM

Concatenating Multiple rows of a column into 1 cell

2803 Views

Hello All,

I'm sure this question has been asked multiple times, but i'm still not have a clear answer..

My Input

State City Arizona Phoenix Arizona Mesa Arizona Tempe Arizona Scottsdale Arizona Tuson Arizona BullHead City Arizona FlagStaff Arizona Globe Washington Vancouver Washington Seattle Washington Richland Washington Olmpia

My output

State City Arizona Phoenix,Mesa,Tempe,Scottsdale,Tuson,BullHead City,BullHead City,FlagStaff,Globe Washington Vancouver,Seattle,Richland,Olmpia

My question : (I have restrictions on accessing SQL in Hana)

Do we have anything in place to do this already for me in Hana (I not able to find one) ?

Does all SQL command work on SQL Script ?

The below statement works for me in Oracle SQL.

"select rtrim(xmlagg(xmlelement(state, city||',')).extract('//text()'))city,state from country group by state"

So Xmlagg .. is it available in Hana ?..

I'm not able to check for i don't have access to SQL view.

Thanks in advance.

Cheers,

Vivek