Problem solve Get help with specific problems with your technologies, process and projects.

Concatenating all columns generically

How do I generically select a row of data and insert a delimeter between each column value? I ultimately want to select into a string variable. I tried using

select * || ','

but this does not work with a wildcard.

Generically, you would have to query the database system's catalog or dictionary or directory or whatever your database system calls it. Using your table name as the query key, select the column names from the catalog table that holds column definitions. Then text-edit the column names back into a concatentated string in SQL like this:

select column1 
    || column2
    || column3
    || columnN
  from yourtable

Selecting information out of the database system's catalog is not difficult, but automating this process to make it generic, so that you could run it on any table name, is more difficult. If you want to generate the concatenated string SQL for only one or two tables, just hardcode it.

If you aren't sure what all the column names are, try this trick:

select * 
  from yourtable
 where 1=0

Many databases will return headers for each column, and then say "0 rows selected" or something similar. You won't have any rows, but you will have the column names from the headers.

For More Information

Dig Deeper on Oracle and SQL