hi, I have below data.

key desc result

L1 L1A Approved

L1 L1B Approvable

L1 L1C A

L2 L2A Denied

L2 L2B Deniable

L2 L2C D

L3 L3A Approved

L3 L3B Denied

Desired result:

Key Result

L1 Approved (since all the L1 entries starts with A%)

L2 Denied (since all the L2 entries starts with D%)

L3 Unknown (since all the L3 entries starts with A% or D%)

How can I achieve this?

I could get to this level:

select systemkey as 'Key',

'Result' = case when count(distinct serviceDisp) > 1 then 'Unknown'

else 'Approved or Denied'

end

from ops.testtbl2

group by systemkey

Above SQL tells me correctly if it is Unknown for L3. But I don't know how to print correctly values for L1 and L2 since they are either Approved or Denied.

Any help is appreciated. Thank you!