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
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.