I have table like the below

All cars in this table will have the only four part_names as shown in the table (windscreen,brakes,tyres and wipers).Based on the diagnosis field I want to categories the cars.

• if a car has all diagnosis as good then outcome is good
• if a car has atleast one diagnosis that is not good and not NULL then the outcome is needs_work
• if a car has all 4 diagnosis as NULL or (a combination of NULL and good diagnosis) then outcome is unknown

Based on the conditions above the outcome field in the resultset will be as follows

• car_1 has 4 good diagnosis so the outcome is good
• car_2 has 2 diagnosis that are not good so the outcome is needs_work
• car_3 has 1 good diagnosis and 3 NULLS so the outcome is unknown
• car_4 has all 4 diagnosis as NULl so the outcome is unknown
• car_5 has one diagnosis that is not good so the outcome is needs-work

so the resultset should be like the table below

I tried with ROW_NUMBER() OVER (PARTITION BY c.car_id ORDER BY c.diagnosis DESC) but I am not getting the desired result I want.

create table carhealthreport
(
  car_id nvarchar(25)
  ,part_name nvarchar(25)
  ,diagnosis nvarchar(25)

)


insert into carhealthreport values ('car_1',  'windscreen' ,'good') 
                                    ,('car_1',  'brakes'     ,'good')
                                    ,('car_1',  'tyres'      ,'good')
                                    ,('car_1',  'wipers'     ,'good')
                                    ,('car_2',  'windscreen' ,'good')
                                    ,('car_2',  'brakes'     ,'good')
                                    ,('car_2',  'tyres'      ,'threadwornout')
                                    ,('car_2',  'wipers'     ,'replacewiper')
                                    ,('car_3',  'windscreen' ,'good')
                                    ,('car_3',  'brakes'     ,NULL)
                                    ,('car_3',  'tyres'      ,NULL)
                                    ,('car_3',  'wipers'     ,NULL)
                                    ,('car_4',  'windscreen' ,NULL)
                                    ,('car_4',  'brakes'     ,NULL)
                                    ,('car_4',  'tyres'      ,NULL)
                                    ,('car_4',  'wipers'     ,NULL)
                                    ,('car_5',  'windscreen','chipped')
                                    ,('car_5',  'brakes'    ,NULL)
                                    ,('car_5',  'tyres'     ,NULL)
                                    ,('car_5',  'wipers'    ,NULL)

CodePudding user response：

I think you can get your desired results with just an aggregate and conditional case expressions

select car_id,
    case when Sum(case when diagnosis='good' then 1 end) =4 then 'good' 
    else
        case when Sum(case when IsNull(diagnosis,'good')='good' then 1 end) =4 then 'unknown'
        else 'needs_work'
        end
    end Outcome
from carhealthreport
group by car_id
order by car_id

Demo Fiddle

This assumes always 4 rows per car_Id, if this is variable you could use count(*).