I need to write a SQL query that cant pull multiple records (from one table) into one record.

There are ways to concatenate values into a single variable, but I don't think that's what you're looking for.  If you're trying to group the results for output/display in a front end application, then I recommend doing the grouping in the front end application - not in the database.  Trying to do it with sql is often more trouble and resource intensive than its worth.  

Solution from aneesh is the one we used many times

@aneeshattingal,

should't you pass in the record @id instead of the name?  

@vnvk,

Yes, the coalesce trick is good. Thats what I was alluding to earlier. But I still maintain its a task for front-end applications   ;-)

I'm curious, how do you find performance? I've always found these types of functions to be a bit slow. So I tend not to use them.  What has been your experience?

insert into destinationTable(Name, records)
select Name, dbo.ConcatList(Name)
from yourtable
group by name

Scratch what I said about the @id. That's obviously wrong :)

When I used them, I found a large increase in performance compared to manipuations in the front end

SQL 2005 is more powerful than 2000 and we found increased performance in using the above technique. We tried using cursors as well as doing the changes from front, but this proved to be the best, at all times, irrespective of the size of the database or the amount of records retrieved.

aneeshattingal:
Can you do this with a select only (I can't Insert)
The code below gives me nothing. What am I doing wrong

declare @res varchar(8000)
  select @res = coalesce(@res + ',', '') + [description]
  from dbo.tmshtData
  where userid = 12345

@vnvk,

That may be the difference.  I spend most of my time on 2000 where they seem to be slower with larger sets.  (We tried cursors too)  I would be *very* pleased if 2005 handles it better than 2000.  I think I'll revisit the idea and will do some tests next time I'm on 2005.  Thanks!

@quest_capital,

You mean you cannot create functions?

_agx_:
no only selects this is on prodution

Oh.  I'm not sure I know of a way to it then (at least not on 2000).  The problem is you cannot assign a @variable and select columns in the same statement.  Thats why you need the UDF (function). I assume that same restriction applies to 2005.