current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 2 down vote favorite
1

enter image description here

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,

enter image description here

I can't figure out how to make that result using an SQL query.

share|improve this question
up vote 3 down vote accepted

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
share|improve this answer
    
this is perfect! I wasn't able to figure out the same when using UNION. Thanks so much! – tinker yesterday
    
@tinker: You are welcome, happy to help. – Arulkumar yesterday
up vote 2 down vote

Just use a constant

SELECT distinct [product_group] , 'Alltype'  
FROM [Yield_Reporting].[dbo].[d_product]
share|improve this answer
    
Thanks for answering! The answer is here correct here too. But the other answer by ArulKumar is more complete with UNION. – tinker yesterday
up vote 1 down vote

You can also do this with GROUPING SETS, eg

SELECT [product_group], ISNULL( [product_type_name] , 'AllType' ) product_group
FROM @d_product
GROUP BY GROUPING SETS ( ( product_group, [product_type_name] ), product_group )

This results in only one table/index scan rather than the two required for the UNION, although I don't see much difference in my performance test. Thanks to @Arulkumar for DDL/DML which I've used for this example.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.