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

• Dozens more answers to tough SQL questions from Rudy Limeback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.