Ask the Expert

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

    Requires Free Membership to View

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

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: