I have a table temp like this

    temp_id temp_name
      1 shekhar
      1 ranjan
      1 ram
      2 manoj
      ............
      ....... ...
Now I want to create a store procedure which return values like this

       temp1_id temp1_name
          1 shekhar, ranjan, ram
          2 manoj,.....
         .............

please help me
Thanks in advance

SHEKHAR

Look here:

http://www.sqlteam.com/article/using...limited-string

Heh... one of my favorite articles on the subject is in the following link. It's a pretty good "How To" and has some information on things to avoid.

http://www.sqlservercentral.com/arti...st+Data/61572/

--Jeff Moden

Well, gee...no wonder you like it. <grin/>

My standard answer to this question--which I get really tired of repeating--is to do the concatenation in whatever language/report generator you are using that *invokes* the SQL.

Almost surely more efficient than doing it in the SQL query, since SQL wasn't designed for this.

But sometimes I just get lazy and let people dig their own graves.

Heh... I'm not partial or anything, huh?

I agree with you on letting the client do the concatenation instead of the SQL Server, but not for the same reason as you... server is just one box with many clients making demands on it... let the clients do some of the work.

I've not tested the GUI method (swore off GUI's 5 yers ago) against the server method for performance. As you can see in the article, a properly written concatenation on the server side is pretty darned fast. It would be an interesting test.

--Jeff Moden

I suspect that the full answer may lie in how much data ends up being duplicated when the concatenation is done in the client. But I don't think that whether the client is GUI-based or perhaps just a command line or maybe a printed report.

Anyway, for this simple example, where the records are nothing but an id and a string, I'm betting on the client side. After all, the code is *SO* dirt simple. Pseudo code:

reccords = execute("select id, name from table order by id, name")
id = records("id")
list = ""
do until no more records
    if records("id") != id
        output list
        list = ""
        id = records("id")
    end
    list += records("name")
    next record
loop
output list

Can't get much simpler (or faster) than that.

Yo9u say it can't get much faster... does that mean you have, say, a million row test that can be programmatically duplicated? Not busting chops here... I'm just a performance freak and am always interested in the method with the best performance. I'm also a genuine data troll and haven't even loaded anything like visual studio so I normally end up writing the test table code and a T-SQL solution and let someone else do the testing. It also keeps people from thinking I put my thumb on the scale.

--Jeff Moden

Oh, sorry...I'm assuming that the SQL query to *GET* this data has already been optimized as far as it can. So the only thing I'm doing here is, effectively, the final "Coalesce" (or equivalent).

Heck, if I had a million rows to process, I would *NEVER* do it in anything but SQL. This is strictly for the typical query that returns a handful of records (might be a handful selected from millions, of course) and is then displaying the delimited list (in a web page, in a report, whatever).

I can't think of any excuse for ever needing to create a set of records where one field contained a delimited list for any purpose *except* display. It obviously makes no sense to do such a query for the purpose of saving the results in some table or passing the results to some other query or or or. At least I can't think of any valid reason to do so. Have you actually encountered such a case?

Heh... ok. I get it.

--Jeff Moden