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

Joining elements from several rows into one column

How can I join elements from several rows into one column? For example, I have a table with

1, dog, big
2, dog, wild
3, dog, mutt

I want to create a table/view that has

1, dog, big wild mutt

There's no satisfactory way to do this in SQL for the general case where you don't know how many rows there will be. If it's three at most, and only three, as in your example --

 animalRow  animalName  animalDesc
     1       dog         big
     2       dog         wild
     3       dog         mutt

then you could try the following query which uses two outer self-joins --

select a.animalRow, a.animalName
     , a.animalDesc || ' ' || b.animalDesc || ' ' || c.animalDesc
  from animals a
 left outer join animals b on a.animalName = b.animalName
 left outer join animals c on a.animalName = c.animalName
 where a.animalRow =
       ( select min(animalRow) from animals     
          where animalName = a.animalName )

Note that an outer join returns nulls for columns from tables where no matching row was found, so you may end up with some trailing blanks. The correlated subselect is required to get the first animalRow number of all rows with the same animalName, and to prevent multiple combinations from appearing in the result.

As you can imagine, this gets very ugly very fast if you want to extend this to more than three rows.

The best way to handle this situation is by looping over the result set in your application. For an example of how this might be done in Cold Fusion, see my previous answer Denormalizing a result set 11 June 2001.

This was last published in February 2002

Dig Deeper on Oracle and SQL



Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.