How to merge 2 or more columns into one?

Question

I have a real problem with one task in SQL concerning merging data from 2 and more columns into 1 column in the most effective way.

id   column1   column2  column3 
1    ok                    notOK
2    
3    abraka     dabrra
4    miew                    haf

and I need to merge 3 comments into 1 comment column like this

id   comments
1    ok                    
1    notOK
2    
3    abraka     
3    dabrra
4    miew                    
4 haf

Now I do it manually through insert into table where I have id and comments columns and I have to sort out data from the primary table. It is really time-consuming, especially when I have at least 8 comments columns that I want to merge.

Answer 1

Try this query

Select Id, Comments 
From 
(
    Select Id, Column1 Comments From MyTable Where Column1 Is Not Null
    Union All
    Select Id, Column2 Comments From MyTable Where Column2 Is Not Null
    Union All
    Select Id, Column3 Comments From MyTable Where Column3 Is Not Null
) DerivedTable
Order by Id