I'm trying to combine cities based on states.
E.g
cities states
Chicago IL
Skokie IL
Houston TX
The output should be as follows;
cities states
Chicago, Skokie IL
Houston TX
I'm using following code;
select members,
companies,
listagg(cities, ',') within group (order by cities) over (partition by states) as cities,
from location
This code is giving me an error that "not a group by expression". The reason I'm using partition by clause because I don't want to members and companies.
Any suggestions will be greatly appreciated. Thanks!