sql - Oracle: Combining similar names together -

sql - Oracle: Combining similar names together -

itemno name requested qty 850045 michael 46 1045 850045 michael jackson 38 834 850045 larry shearin 22 473 850045 michael jackson 11 233 850045 larry 5 84

i have table requester name not normalized. michael , michael jack same person. larry , larry shearin same person. there way combine row info requested , qty sum correctly? thinking there might sort of oracle function or analytic this...

itemno name requested qty 850045 michael jackson 95 2112 850045 larry shearin 27 557

there may way, should work using upper , matching firstname (without spaces) fullname (with space) -- if multiple total names match, you're results inaccurate.

select t.itemno, t.name, t.requested + t2.requested requested, t.qty + t2.qty qty ( select itemno, upper(name) name, sum(requested) requested, sum(qty) qty yourtable name '% %' grouping itemno, upper(name) ) t bring together ( select itemno, upper(name) name, sum(requested) requested, sum(qty) qty yourtable name not '% %' grouping itemno, upper(name) ) t2 on t.itemno = t2.itemno , t.name t2.name||' %'

here sql fiddle.

and here results:

itemno name requested qty 850045 michael jackson 95 2112 850045 larry shearin 27 557

i assume you're total (32) larry mistaken above (22 + 5)?

hope helps.

sql oracle normalization