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...

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.