GROUP BY with string concatenation :-) 1

[jbutton]

Hello,

I have a situation where it would be wonderful if I could GROUP BY a string field and have it concatenate the strings comma-delimited much like it would group an integer field by adding them. I don't know if this is possible in SQLServer, but would like some direction if anyone thinks it might be. Thank you in advance!

 

[vbkris]

1. one way would be to use cursors (takes least coding time, but huge server load)

2. use while loops to read through the table. (takes huge coding time, but lesser serve load)...

Known is handfull, Unknown is worldfull

 

[bborissov]

SELECT MyStringField+CAST(SomeIntField as varchar(xx)) AS Test
       FROM MyTable
GROUP BY MyStringField+CAST(SomeIntField as varchar(xx))

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[jbutton]

bborissov,

i believe that solution will only work if the strings are of numeric value. thank you very much but i need something for text. thanks again though!

 

[jbutton]

thank you vbkris. i am going to do this with cursors.

 

[bborissov]

could you post some example? maytbe I didn't undestood something.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[jbutton]

sorry Borislav for not being clear. My problem is i have data like this:

categoryId categoryAuthor
1 Jim
1 Josh
1 Dave
2 Bob
2 Janet
2 Susie
3 Danielle
3 Kaite
4 Cassi

I want it to look like this:

categoryId categoryAuthor
1 Jim, Josh, Dave
2 Bob, Janet Susie
3 Danielle, Kaite
4 Cassie

 

[gmmastros]

Take a look at this thread. I think you'll find it helpful. thread183-1159740

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom