Q

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 first published in February 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