3

i need to write a query that combines multiple columns of text.

my database is a database of wallpapers on my computer (i use john's background switcher), so i can easily find them and have them categorized.

each record has 4 character fields (i figured it was easier to do this than a tag style thing.) i need a query to merge these so all characters from all 4 different fields are in one column, an not merged into one field. please remember that some of the character fields are empty.

also, if there is an easier way to do this, please notify me.

here is a sample of what i want:

(table)
col1|col2|col3    
x   |y   |z    
c   |    |    

(query output)    
outputcol    
x    
y
z  
c
1
  • 1
    I 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.
    – wfaulk
    Oct 3, 2009 at 14:25

3 Answers 3

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.

3

@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.

7
  • +1. Indeed. Might want to include a WHERE clause to eliminate null values if these are expected in the columns.
    – A Dwarf
    Oct 3, 2009 at 14:59
  • I 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.
    – pipTheGeek
    Oct 3, 2009 at 15:02
  • And 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.
    – A Dwarf
    Oct 3, 2009 at 15:11
  • that works mostly, it includes some empty records and some repeats, but i can fix that thank you.
    – crazybmanp
    Oct 3, 2009 at 15:12
  • actualy, wait, this just puts the strings together, each different field needs its own record in the query.
    – crazybmanp
    Oct 3, 2009 at 15:57
1
SELECT (Column1 + Column2 + Column3 + Column4)
FROM YourTable

The above will concatenate all four columns. If you need a space separator:

SELECT (Column1 + " " + Column2 + " " + Column3 + " " + Column4)
FROM YourTable

Thus the plus sign works as a concatenator. Finally, if you need to name the resulting column:

SELECT (Column1 + Column2 + Column3 + Column4) AS ColumnName
FROM YourTable

One last note, been ages since I last worked on Access, but I reckon you may also be able to concatenate fields with the || operator:

SELECT (Column1||Column2||Column3||Column4)
FROM YourTable

Following on the comments to this answer, different database engines may provide different syntaxes. An annoyance, no doubt:

SELECT Column1 & Column2 & Column3 & Column4
FROM YourTable

Here we replaced '+' with the usual '&' concatenation symbol present in many windows applications.

Cheers

4
  • for some reason this yields a table full of blank values equal to the number of records in the first table.
    – crazybmanp
    Oct 3, 2009 at 14:42
  • errm... 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
    – A Dwarf
    Oct 3, 2009 at 14:46
  • yes i did replace all of the tags; all of the columns the column name and yourtable
    – crazybmanp
    Oct 3, 2009 at 14:48
  • Ok. Editing my answer to include other possibilities. A moment...
    – A Dwarf
    Oct 3, 2009 at 14:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .