MySQL 5.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE products (
  `ProdID` INTEGER,
  `ProdName` VARCHAR(6),
  `Team` VARCHAR(6)
);

INSERT INTO products
  (`ProdID`, `ProdName`, `Team`)
VALUES
  ('1', 'AAA', null),
  ('1', 'AAA_T1', 'T1'),
  ('2', 'BBB', null),
  ('3', 'CCC', null),
  ('3', 'CCC_T1', 'T1'),
  ('3', 'CCC_T2', 'T2'),
  ('4', 'DDD', null),
  ('4', 'DDD_T3', 'T3');
select max(case when team = 'T1' or team is null then prodname end) prodname
from products
group by prodid


prodname


AAA_T1

BBB

CCC_T1

DDD


select max(case when team = 'T2' or team is null then prodname end) prodname
from products
group by prodid


prodname


AAA

BBB

CCC_T2

DDD


select max(case when team = 'T3' or team is null then prodname end) prodname
from products
group by prodid


prodname


AAA

BBB

CCC

DDD_T3