2

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.

Dijkgraaf
  • 11,049
  • 17
  • 42
  • 54
HeadOverFeet
  • 768
  • 6
  • 13
  • 33
  • 1
    Could you clarify what you're trying to do? Are you trying to change the schema of a table (so that it uses one column instead of three), or are you trying to select data from multiple columns in one or more tables, and insert that data into a single column in another table? – Dan J Jun 23 '15 at 18:48
  • Why do you have the `Blank` show up for `2`, but not for `1`, `3`, or `4`? – Raj More Jun 23 '15 at 18:52
  • Please tell me you are doing this as part of a normalization project so you don't need to keep your data in that poor design. – Sean Lange Jun 23 '15 at 19:14
  • Hi, I need it like that because I need it for another script in a decision table. There are columns with null value and I need to keep them in the script = nto to loose them. I am trying to merge data from 3 columns into one. – HeadOverFeet Jun 24 '15 at 06:53

1 Answers1

4

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
Raj More
  • 47,048
  • 33
  • 131
  • 198
  • Hello, thank you very much. The only problem with this query is that is does not return the rows where there is no comment in any of the Comment colums. – HeadOverFeet Jun 24 '15 at 06:48
  • 1
    I have solved it through insert into table and extra column with null values :) thank you once again for help :) – HeadOverFeet Jun 24 '15 at 09:45