How to combine multiple columns into one column?

Question

I'm writing a query and want the results in one column My current results return like this

Column1     Column2    column3
1              A         CAT

I want the results to return like this

Column1
1
A
CAT

Answer 1

SELECT Column1 FROM TableName
UNION ALL
SELECT Column2 FROM TableName
UNION ALL
SELECT Column3 FROM TableName

If you don't want duplicate values, use UNION instead of UNION ALL.

You can also do this using UNPIVOT operator

SELECT Column123
FROM
(
  SELECT Column1, Column2, Column3 
  FROM TableName
) AS tmp
UNPIVOT 
(
  Column123 FOR ColumnAll IN (Column1, Column2, Column3)
) AS unpvt;

https://www.w3schools.com/sql/sql_union.asp
https://www.mssqltips.com/sqlservertip/3000/use-sql-servers-unpivot-operator-to-help-normalize-output/

Answer 2

The answer is.. it depends..

If the number of columns are unknown.. then use unpivot as UZI has suggested

if you know all columns and is a small finite set..

you can simply go

Select
column1
from table

union all
select column2
from table

union all
select column3
from table