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.
Oracle White Papers: Fusion Middleware