Case statement help with parent child relationship

1

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!

2 
SELECT [key],
    CASE WHEN SUM(CASE WHEN result LIKE 'A%' THEN 1 ELSE 0 END) = COUNT(*)
         THEN 'Approved'
         WHEN SUM(CASE WHEN result LIKE 'D%' THEN 1 ELSE 0 END) = COUNT(*)
         THEN 'Denied'
         ELSE 'Unknown' END AS result
FROM #data
GROUP BY [key]

--btw, it's considered good netiquette to provide sample data in a directly usable format, like this:

CREATE TABLE #data (
    [key] varchar(10) NOT NULL,
    [desc] varchar(10) NOT NULL,
    result varchar(30) NULL
    )
INSERT INTO #data VALUES
('L1', 'L1A', 'Approved'),
('L1', 'L1B', 'Approvable'),
('L1', 'L1C', 'A'),
('L2', 'L2A', 'Denied'),
('L2', 'L2B', 'Deniable'),
('L2', 'L2C', 'D'),
('L3', 'L3A', 'Approved'),
('L3', 'L3B', 'Denied')
2 Likes
3

Thanks a lot scott. yes, i agree, i will keep this in mind while providing sample data in future. Thanks again!