How can I combine 3 columns into one-CodePudding

I have 3 columns called product_one, product_two and product_three and I want to combine all of them into one.

From this:

|product_one | product_two | product_three |
|------------------------------------------|
|spoon       | phone       | knife
|fork        | case        |

To this:

|products| 
|--------
|spoon   |  
|fork    | 
|phone   |
|case    |
|knife   |

How is this possible in sql?

CodePudding user response：

select t.product_one 
from your_table t
  union all
select t2.product_two
from your_table t2
  union all
select t3.product_three
from your_table t3

CodePudding user response：

Consider below approach

select products from your_table 
unpivot (products for col in (product_one, product_two, product_three))

if applied to sample data in your question - output is

CodePudding user response：

By union: select a.product_one as pr from a where a.product_one is not null union select b.product_two as pr from b where b.product_two is not null union select c.product_three as pr from c where c.product_three is not null