MSSQL - select individual products that are 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 can I return a complete list of unique products in one column? Desired result below:

**Products**
Apple
Banana
Orange
Pear

If I really choose excellent, I obviously won’t get a pear because it is not included in the ProductA column.

Any help would be greatly appreciated. Thank!

Answer 1

You can combine them together as one column:

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