How to flatten relationship

[SueRobbins]

Having trouble with a query. Here's a simplified version of the two tables involved:

Table - "Name"
NameID Name
001 Brown
002 Smith
003 Jones
004 Adams

Table - "Color"
ColorID NameID Year Color
001 001 2003 blue
002 001 2004 blue
003 001 2004 green
004 002 2003 blue
005 002 2004 red
006 003 2003 blue
007 004 2003 blue
008 004 2004 yellow
009 004 2004 blue
010 004 2004 green

I want the end result of the query to show name and all colors for a given year, but I need the color(s) to be formated as a single string. So, for 2004 the result should be:
Name Colors
Brown blue,green
Smith red
Adams yellow,blue,green

Can someone steer me in the right direction to accomplish this?

 

[r937]

this is really hard to do, so just do this in your application layer, not your database layer

except in sybase ase and mysql, where it's trivially easy



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)