asked on
concatenating values from multiple rows onto one column without grouping - oracle sql
i have a dataset which records family members. see attached fig1.
![fig1]()
I need some syntax that will out put the results as mocked up in fig2.
![fig2]()
the last column family_surnames pulls out the distinct client_surnames in the family and concatenates them.
We're using oracle 10 so cant use list_agg. List_agg looks like it would have done the job (as shown here http://www.exforsys.com/tu
any help is appreciated
I need some syntax that will out put the results as mocked up in fig2.
the last column family_surnames pulls out the distinct client_surnames in the family and concatenates them.
We're using oracle 10 so cant use list_agg. List_agg looks like it would have done the job (as shown here http://www.exforsys.com/tu
any help is appreciated
Oracle DatabaseSQL
Last Comment
slightwv (䄆 Netminder)
You may use the WM_CONCAT built-in function ;-)
BUT: be aware!!
Taken from: https://oracle-base.com/articles/misc/string-aggregation-techniques
BUT: be aware!!
WM_CONCAT is an undocumented function and as such is not supported by Oracle for user applications (MOS Note ID 1336219.1). If this concerns you, use a User-Defined Aggregate Function described below.
Also, WM_CONCAT has been removed from 12c onward, so you can't pick this option.
Taken from: https://oracle-base.com/articles/misc/string-aggregation-techniques
ASKER
PortletPaul - no you can propose group by if it gives me the desired result. so long as it'll work in oracle 10.
Alexander Eßer [Alex140181] - Thanks for the link. Using wm_concat as suggested in the examples would group by family id so show only one row per family id (7 rows). i need the query to retrun all (15) rows
Alexander Eßer [Alex140181] - Thanks for the link. Using wm_concat as suggested in the examples would group by family id so show only one row per family id (7 rows). i need the query to retrun all (15) rows
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Note - this will produce Bellamy/Smith for fam_id = 6 although that's not what your mocked up results showI suppose he just simply missed that one when editing the result grid for the mock ups ;-)
That was my assumption as well :-)
ASKER
thanks
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
why are you excluding group by? if you get the desired result does it matter if we propose group by?