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