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
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