Is there a way to consolidate data on multiple rows into one? For example, suppose a query for people's favorite foods comes up with these rows:
Name Food John Fruit John Banana John Bread Mary Banana Mary Pizza
Assuming that we cannot change the basic table design, is there a way to come up with a response like
Name Food John Fruit, Banana, Bread Mary Banana, Pizza
Requires Free Membership to View
Yes, there is a way, but only if you know in advance what those foods will be.
This is a very frequent question, and my first reply is always to try to do it in the application code by "looping over" the original result set. Note that an ORDER BY is assumed, and that "looping over" means using whatever mechanism the programming language or script might have. For example, in ColdFusion you can say:
<cfoutput query="originalquery" group="Name">
#originalquery.Name#
<cfset comma="">
<cfoutput>
#comma##originalquery.Food#
<cfset comma=", ">
</cfoutput>
</cfoutput>
The GROUP= parameter of the outer CFOUTPUT implements the "current/previous" logic which in other languages you'd have to write yourself. The COMMA variable is set for each new Name and is reset inside the nested CFOUTPUT after the first Food has been listed.
To come back to your original question, yes, there is a way to do this with SQL, but only if the "many" values are known in advance, and there aren't too many of them to make the query awkward.
Consider the following query:
select Name
, case when Food='Fruit'
then 'Fruit'
else '' end as Fruit
, case when Food='Banana'
then 'Banana'
else '' end as Banana
, case when Food='Bread'
then 'Bread'
else '' end as Bread
, case when Food='Pizza'
then 'Pizza'
else '' end as Pizza
from yourtable
This produces the following output:
Name Fruit Banana Bread Pizza John Fruit John Banana John Bread Mary Banana Mary Pizza
Using the query above as a derived table, you can collapse the columns using the MAX aggregate function while concatenting them into a single column:
select Name
, max(Fruit)
||' '||max(Banana)
||' '||max(Bread)
||' '||max(Pizza) as Food
from ( select Name
, case when Food='Fruit'
then 'Fruit'
else '' end as Fruit
, case when Food='Banana'
then 'Banana'
else '' end as Banana
, case when Food='Bread'
then 'Bread'
else '' end as Bread
, case when Food='Pizza'
then 'Pizza'
else '' end as Pizza
from yourtable
) as ZZZ
group by Name
This produces the following output:
Name Food John Fruit Banana Bread Mary Banana Pizza
Unless the output is destined for HTML, where white space collapses automatically, you may also want to remove leading spaces and optionally replace embedded spaces with a comma and space. This is easy in some databases, harder in others.
This was first published in August 2003
Join the conversationComment
Share
Comments
Results
Contribute to the conversation