Trying to not use Cursor

[jmikow]

I have a query that I need to construct and I'm trying to do it without a cursor, if possible.

I have a table (it's actually a view) that I need to combine multiple rows so that I can retrieve a single row of data.

My table is similar to the following:
Col1 Col2
1234 123456789
1234 987654321
1234 654987321

I need a query that will return the following:
Col1 Col2
1234 123456789,987654321,654987321

What would be the best way to go about this? I've read that cursors are bad, but I'm afraid that it might be the only way to go if I need to get the comma delimited data into a single column.

Any ideas would be appreciated.

Thanks,

Josh

 

[gmmastros]

Here's a 'non-cursor' way to accomplish this.

Declare @Output VarChar(8000)

Select 	@Output = Coalesce(@Output + ',', '') + Col2 
from 	<tableName>
Where	Col1 = 1234

-George

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

 

[jmikow]

George,

Thanks for the example!

That works great. I think I'll need to use it in a UDF to get it to work with the selecting of the rest of the columns at the same time.

Thanks again for the guidance on this.

Josh

 

[gmmastros]

No problem. Glad it helped. And to be honest, I just recently learned this technique from vongrunt.

-George

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

 

[jmikow]

It's great when little tips like this can be passed on. It saved me many hours of trying to figure it out.

I'm using it in a UDF so I can include it in my select statement.

Thanks again!

Josh