MSSQL - Select distinct products that are found in two columns

Question

Suppose I have the following combinations in my dataset:

**ProductA**            **ProductB**
  Apple                   Banana
  Apple                   Orange
  Apple                   Pear
  Banana                  Orange
  Banana                  Pear
  Orange                  Pear

How would I return a complete list of unique products in a single column? Desired output below:

**Products**
Apple
Banana
Orange
Pear

If I do select distinct, I obviously won't get the pear because it's not included in column ProductA.

Any help would be appreciated. Thanks!

Answer 1

You can UNION them together as a single column:

SELECT  ProductA AS Products
FROM    tablename
UNION
SELECT  ProductB
FROM    tablename;