The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

T-SQL group by and concatenated strings

I've got a table with 2 columns: colA int, colB varchar. The data in this table is as follows

11 red
11 blue
11 green
22 square
22 circle

Can anyone help me write a query that will output 2 rows like this:

11 red, blue, green
22 square, circle

Any help with this one would be muchly appreciated.
Thanks.
help wanted Send private email
Wednesday, October 18, 2006
 
 
I can't think of a way to do it in plain SQL. Since you asked specifically for a T-SQL solution, there probably is a specific, proprietary database function that will concatenate all records (matching a criteria) together as a string.

The easiest thing to do, of course, is wait for someone to give you the answer. The second easiest thing to do is find a list of all proprietary functions for your database (example: if you were using Oracle, check your PL/SQL Programming reference book) and just look for one that sounds like it might do what you're trying to do.

Sorry about the answer - like j said, this sounds suspiciously like a homework problem, and the real value is that it teaches you to look stuff up as opposed to solving the puzzle itself.
TheDavid
Wednesday, October 18, 2006
 
 
Actually, this is something I've wanted in Oracle for a long time, a group by concatenator function for strings. I don't think that it exists; I think that you'd have to write a procedure with cursors.
Steve Hirsch Send private email
Wednesday, October 18, 2006
 
 
Steve,

tom Kyte has a bunch of methods here: http://tkyte.blogspot.com/2006/08/evolution.html
David Aldridge Send private email
Wednesday, October 18, 2006
 
 
For what it's worth, this is not a homework question. I've given a very simplified example so that the complexities of my specific situation don't prevent people from being able to help me with the crux of the problem.

If you're just going to tell me to do my own homework, why bother commenting at all?
help wanted Send private email
Thursday, October 19, 2006
 
 
help wanted Send private email
Thursday, October 19, 2006
 
 
You can do this with COALESCE.

Example -
http://www.sqlteam.com/item.asp?ItemID=2368
Justin Send private email
Thursday, October 19, 2006
 
 
Thanks, learned something! Never used the START WITH feature. Hmmm!
Steve Hirsch Send private email
Thursday, October 19, 2006
 
 
If you're using SQL Server 2005 you can use the new PIVOT and UNPIVOT functions.  They do what you want!
Don Kitchen Send private email
Friday, October 20, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz