Problem solve Get help with specific problems with your technologies, process and projects.

Collapsing a one-to-many result set

An Oracle user asks how to do looping in SQL and collapse a one-to-many result set.

I am trying to figure out how to do looping in SQL. I have a table like the one below:

ID      System  Material 
1       Floor   Wood 
1       Floor   Carpet 
1       Wall    Dry wall 
1       Wall    Paper 
2       Floor   Wood 
2       Floor   Carpet 

I'd like to end up with results like:

ID      System  Material 
1       Floor   Wood, Carpet 
1       Wall    Dry Wall, Paper 
2       Floor   Wood, Carpet 

While this is a very common requirement, there is no satisfactory answer for all database systems.

In SQL FAQ: Common SQL questions, part 3 (05 July 2007), under the heading Comma-delimited string questions, are links to previous questions where we've discussed this before. Skip the first one. The second one gives an example of MySQL's GROUP_CONCAT function, and if you're using MySQL, this is all you need.

Stringing together columns with UPDATE SQL (24 August 2007) shows one method involving a temporary table. (Messy, isn't it? Best I could do at the time.) Creating comma-delimited strings, written by SQLBlindman, is much more elegant.

Dig Deeper on Oracle development languages

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.