Add additional rows using an SQL query SELECT distinct [product_group]
      ,[product_type_name]
  FROM [Yield_Reporting].[dbo].[d_product]


For every product_group entry I want to have another product_type_name = AllType

So the resulting table should look like this,



I can't figure out how to make that result using an SQL query.
You can hard-code AllType string for the product_type_name value and  UNION this with your existing DISTINCT values you can get your expected result.

Working Demo with the given sample data:

DECLARE @d_product TABLE (product_group VARCHAR (200), product_type_name VARCHAR (200));

INSERT INTO @d_product( product_group, product_type_name) VALUES
('BARE_SILICON', 'BARE_SILICON'),
('Black_Mamba', 'Black_Mamba'),
('BURIN-IN_WAFER', 'BURIN-IN_WAFER'),
('CALVIN', 'CALVIN_BLK');

SELECT DISTINCT [product_group], [product_type_name] FROM @d_product
UNION 
SELECT DISTINCT [product_group], 'AllType' FROM @d_product


Output:

product_group      product_type_name
---------------------------------------
BARE_SILICON       AllType
BARE_SILICON       BARE_SILICON
Black_Mamba        AllType
Black_Mamba        Black_Mamba
BURIN-IN_WAFER     AllType
BURIN-IN_WAFER     BURIN-IN_WAFER
CALVIN             AllType
CALVIN             CALVIN_BLK