Ask the Expert

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.


    Requires Free Membership to View

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


This was first published in November 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: