Consolidate data on multiple rows into one
Is there a way to consolidate data on multiple rows into one? For example, suppose a query for people's favorite...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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
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.
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.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments