How to reduce 10+ union select statements

Question

I need to extract one column of data from 10+ columns from one table. Instead of adding up 10+ union select statements, is there another way to do this without repeating union select statements for each column?

I can get it by putting more than 10 sentences in it, as shown below:

select 'column_5' from table_a union select 'column_6' from table_a union select 'column_7' from table_a union . . . union select 'column_18' from table_a 

Thanks for your time in advance :)

Answer 1

Using Cross Apply or UnPivot , you can avoid multiple UNION statements

 select Distinct COL from table Cross apply ( values (column_1), (column_2), .. .. (column_18) ) CS (COL) 

Note: Since you used UNION , I saved Distinct to select . If you do not want to remove duplicates, remove Distinct from select

Answer 2

A query with UNPIVOT will look something like this.

 SELECT * FROM TableName t UNPIVOT (Vals FOR N IN (Column1, Column2, Column3,....,Column10))up 

It is important to note that all columns in an IN expression must have the same data type if they do not use a subquery to convert them to a homogeneous data type and then not open something like ...

 SELECT * FROM ( SELECT CAST(Column1 AS VARCHAR(200)) AS Column1 ,CAST(Column2 AS VARCHAR(200)) AS Column2 ,CAST(Column3 AS VARCHAR(200)) AS Column3 ,..... ,CAST(Column10 AS VARCHAR(200)) AS Column10 FROM TableName) t UNPIVOT (Vals FOR N IN (Column1, Column2, Column3,....,Column10))up