sqlsql-server-2017

SQL pick condition string case by case grouping


this is my data where Processname and ProformaInvoiceNum. data is group by progrmainvoicenum.

ProcessName ProformaInvoiceNum
TOP CUTTING SHE/PBK/23/10001.1
Back Cutting SHE/PBK/23/10001.1
LINING CUTTING SHE/PBK/23/10001.1
Cutting SHE/SHR/22/10155.1
TOP CUTTING SHE/REHRM/22/10026.4
Cutting SHE/REHRM/22/10047.4
Recutting SHE/REHRM/22/10047.4
TOP CUTTING SHE/SHR/22/10308.1
Cutting SHE/SHR/22/10308.1

i need a new column name of main process. if proformainvoicenum have process name 'top cutting' then on that main process feild value show top cutting in that group proformainvoicenum. also if 'top cutting' not exit on that group and 'cutting' is there then 'cutting will be comes on that group profromainvoicenum like below table expample

ProcessName ProformaInvoiceNum Main Process
TOP CUTTING SHE/PBK/23/10001.1 TOP CUTTING
Back Cutting SHE/PBK/23/10001.1 TOP CUTTING
LINING CUTTING SHE/PBK/23/10001.1 TOP CUTTING
Cutting SHE/SHR/22/10155.1 Cutting
TOP CUTTING SHE/REHRM/22/10026.4 TOP CUTTING
Cutting SHE/REHRM/22/10047.4 Cutting
Recutting SHE/REHRM/22/10047.4 Cutting
TOP CUTTING SHE/SHR/22/10308.1 TOP CUTTING
Cutting SHE/SHR/22/10308.1 TOP CUTTING

please help to achived the result


Solution

  • Use a case expression with exists sub-query to check for the required condition

    select 
    case when exists 
              (
                  select * 
                  from   yourtable x 
                  where  x.ProformaInvoiceNum = t.ProformaInvoiceNum
                  and    x.ProcessName = 'TOP CUTTING'
              ) 
         then 'TOP CUTTING'  
         when exists 
              (
                  select * 
                  from   yourtable x 
                  where  x.ProformaInvoiceNum = t.ProformaInvoiceNum
                  and    x.ProcessName = 'Cutting'
              ) 
         then 'Cutting' 
         else 'Front Cut' 
         end as [Main Process]
    from yourtable t