@A Dwarf, SELECT Column1 + Column2 + Column3
will work so long as all the columns are text and non-null.
||
is the concatenation operator for Oracle. For Access it is &
.
So a better statement would be
SELECT Column1 & Column2 & Column3 as NewColumn FROM MyTable
Obviously if you want the space seperator then it becomes
SELECT Column1 & " " & Column2 & " " & Column3 as NewColumn FROM MyTable
The &
operator will treat null values as an empty string.
If you didn't want the extra spaces to appear then you could use something like
SELECT Nz([Column1] + " ","") & Nz([Column2] + " ","") & [Column3]
EDIT:
If you also want to include the original separate columns as well as the joined version then just list them as well, eg.
SELECT Column1 & " " & Column2 & " " & Column3 as NewColumn, Column1, Column2, Column3 FROM MyTable
EDIT after reading the OP's example output.
It appears that we are all doing exactly not what the OP wanted. So to achieve what you are asking for in the example that you have shown you need.
SELECT Column1 FROM MyTable WHERE Column1 Is Not Null and Column1<>""
UNION ALL
SELECT Column2 FROM MyTable WHERE Column2 Is Not Null and Column2<>""
UNION ALL
SELECT Column3 FROM MyTable WHERE Column3 Is Not Null and Column3<>""
If you want to remove duplicates, then you just need to remove the word ALL
, so you get
SELECT Column1 FROM MyTable WHERE Column1 Is Not Null and Column1<>""
UNION
SELECT Column2 FROM MyTable WHERE Column2 Is Not Null and Column2<>""
UNION
SELECT Column3 FROM MyTable WHERE Column3 Is Not Null and Column3<>""
Obviously you can repeat the UNION SELECT...
as many times as you need.