This is simply impossible because you are combining fields of rows that should be independent of each other. You cannot assume any sort order and need information about the contents of each row. Also, you now have some relation between otherwise unrelated data. For example, you assume:
1 NULL NULL
NULL 2 NULL
NULL NULL 3
4 NULL NULL
But it could also easily be:
NULL NULL 3
4 NULL NULL
1 NULL NULL
NULL 2 NULL
Would this yield a desired result?
It looks like you have 2 options:
First is a simple solution to get some result that might even look what you want by simply doing 3 select operations, each to get values for column1, column2 and column3. After that you combine those results to get the end result.
Second, it looks like you are combining columns in a row that shouldn't. It is like you created a separate column for each option but with a constraint that excludes all other options. Example columns: Male, Female, Unknown gender
Maybe a good time to read up on normalization:
http://msdn.microsoft.com/en-us/library/aa933055%28v=sql.80%29.aspx[
^]
http://msdn.microsoft.com/en-us/library/aa291817%28v=vs.71%29.aspx[
^]
Good luck!