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.

Dig Deeper on Oracle and SQL

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.

Please create a username to comment.