Q

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


This was first published in November 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close