Merge multiple columns into one via a query in an access database
Solution 1
Maybe this?
SELECT col1 FROM table
UNION
SELECT col2 FROM table
UNION
SELECT col3 FROM table
UNION
SELECT col4 FROM table
UNION
only joins distinct values (if you have 4 't' values, it only puts one). UNION ALL
will store duplicates.
Solution 2
@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.
Related videos on Youtube
Sanjaal Corps
Updated on September 17, 2022Comments
-
Sanjaal Corps almost 2 years
How do I list all the subversion repos created with
svnadmin create
by various users? I want to setup a method to backup the repositories. But first, I should know a list of them. Many users might have created their own repos into multiple locations.-
wfaulk over 14 yearsI know you tagged it with "access", but it would be nice for you to mention that that's the database you're using in the question text.
-
-
crazybmanp over 14 yearsfor some reason this yields a table full of blank values equal to the number of records in the first table.
-
A Dwarf over 14 yearserrm... did you replace Column1, Column2, etc, with the actual column names? Sorry, I know it's a basic question but I can't see why you are getting blank values
-
crazybmanp over 14 yearsyes i did replace all of the tags; all of the columns the column name and yourtable
-
A Dwarf over 14 yearsOk. Editing my answer to include other possibilities. A moment...
-
A Dwarf over 14 years+1. Indeed. Might want to include a WHERE clause to eliminate null values if these are expected in the columns.
-
Kalaivani over 14 yearsI was assuming that not all columns would be null for a single record, so you probably wouldn't want to exclude them from the results.
-
A Dwarf over 14 yearsAnd you probably assumed correctly. The point being though , if that is not the case, one will want to use a where clause testing for null.
-
crazybmanp over 14 yearsthat works mostly, it includes some empty records and some repeats, but i can fix that thank you.
-
crazybmanp over 14 yearsactualy, wait, this just puts the strings together, each different field needs its own record in the query.
-
Kalaivani over 14 yearsI think you mean each field needs it own column in the query as well as the concatenated one. I have updated my answer to show how to do that.