Demo, MySQL - rextester

Language: Layout:

CREATE TABLE your_table(
   contract_nr INTEGER
  ,id_supplier VARCHAR(4)
  ,id_seller   VARCHAR(4) 
  ,id_vendor   VARCHAR(4) );
INSERT INTO your_table(contract_nr,id_supplier,id_seller,id_vendor) VALUES (1,'1521',NULL,NULL);
INSERT INTO your_table(contract_nr,id_supplier,id_seller,id_vendor) VALUES (1,'1521','2',NULL);
INSERT INTO your_table(contract_nr,id_supplier,id_seller,id_vendor) VALUES (1,NULL,NULL,'538');
INSERT INTO your_table(contract_nr,id_supplier,id_seller,id_vendor) VALUES (2,NULL,NULL,'154');
INSERT INTO your_table(contract_nr,id_supplier,id_seller,id_vendor) VALUES (2,NULL,NULL,'159');
INSERT INTO your_table(contract_nr,id_supplier,id_seller,id_vendor) VALUES (3,'2626','17',NULL);
INSERT INTO your_table(contract_nr,id_supplier,id_seller,id_vendor) VALUES (3,NULL,'17','124');

select
    contract_nr,
    case
        when max(id_supplier) is not null 
                or max(id_seller) is not null
            then 'category1'
        when max(id_vendor) is not null
                and max(id_supplier) is null 
                and max(id_seller) is null
            then 'category2'
    end as category
from your_table
group by contract_nr;


absolute service time: 0,4 sec