How to reduce 10+ union select statements

I need to extract a single column of data from 10+ columns of a single table. Instead of stacking up 10+ union select statements, is there another way of doing this without repeating the union select statements for each column?

I can get it by stacking up 10+ select statements like 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 :)

Answers


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 have used UNION i have kept Distinct in select. If you don't want to remove duplicates then remove Distinct from select


Need Your Help

cannot use specification in findAll method in spring data jpa

java spring jpa

i'm trying to use specification in my repository findAll method but i get this message :

in DotNetNuke - Where is the 'Auto Add Portal Alias' option in the database

dotnetnuke

There is a feature added in DNN 5.6.2 to Auto Add Portal Alias.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.