need the solutions badly...any help please

MSA,

Going by the discussion in this post, you should have got two options. If you are particular about ORDER of the string concatenation and have another column that decides the order, you can stick with CURSOR solution. If order of string concatenation is not a factor, you can use the other posted by defiant. We had the same discussion over the order in the other post too where I posted a similar solution to that of defiant's. Having said that, it is now upto you to choose the one that best fits your need.

cheers.

_________________________
- Vijay G
Strive for Perfection

thanks vijay i will jst try it but if u feel like giving some sample script for my sample data then that would be much easier for me to understand it...thank you so much

Still, how can you tell which one of these two "AA" records to concat first?

aa Thursday morning
aa This is

That is what the discussion is about. we wont have control over it unless you have another column that decides the order of string concatenation though you make up your mind to use the cursors. You can't say even that with the other method.

Cheers.

_________________________
- Vijay G
Strive for Perfection

Ok I will chime in also. If you have control of the tables structure. You should alter the table structure to make it so you don't have to use cursors. The sql language was designed to not require you to go row by row as you can in a cursor. Although I love the power of cursors. You should only use them when you have to or if they make things more efficient. If you add an identity colum to the table structure you plan you can probably avoid needing to use cursors. The way you are going you risk getting:

aa Thursday morning This is
bb Friday morning
cc Saturday morning This is

When this is not what you want. There is no rule to say that if you select the table as you currently designed it that you wont get the following:

ColumnA ColumnB
---------- -----------------------
aa Thursday morning
aa This is
bb Friday morning
cc Saturday morning
cc This is

Remember just because you entered the data in a particular order does not mean that it will be returned in the same order all the time unless you make proper use of a clustered index.

Even then, there is no guarantee that the returned rows will be in the order of the clustered index. The fact that you observe that the rows in a particular resultset are ordered by the clustered index in a simple table and simple query absent any ORDER BY clause does not mean it always happens that way.

The problem with the (idiomatic) statement:

SELECT @stringvar = @stringvar + ', ' + somecolumn
FROM sometable

is there is no way to guarantee that the various rows will be returned in any particular order, clustered index notwithstanding. The (internal) execution of this statement happens way before the resultset is constructed for return back to the client, so it is highly dependant on the execution plan.

Granted, in this very simple example, it almost certainly will be in order by the clustered index. But add a JOIN or two, a subquery or view, maybe an index or two on some other columns, or on the column in question, choose an unusual distribution of data values, have the data in the table be very active (or pinned) so it is cached, etc. and the optimizer may very well decide on a different execution plan and return the rows in some other order. By definition it is free to do that, and there is not a thing you can do about it.

Look. I admit to being pedantic here. You could probably run such a query a gazillion times under all sorts of circumstances and every time you observe it, you see the results in a predictable order.

But, upgrade to the next version, change from SQL Server to some other implementation, and you could be surprised. If the risk of a sudden change in behavior is acceptable, then by all means do what works for you. Just don't complain when you use undocumented behavior and it someday no longer works the way you'd like...

On another pedantic note, it is all I can do to restrain myself from asking why the OP would want to return a result that violated first normal form (atomic data)? It sounds like he is trying to resolve a presentation issue in the database, precisely where it doesn't belong....

Jeff Mason
Custom Apps, Inc.
[email protected]

That is more than an answer to the question posted by the OP and msa, Jeff.

_________________________
- Vijay G
Strive for Perfection

Perhaps I'm a little slow this morning - please forgive me. Your post comes across as critical. If what I wrote "...is more than an answer to the question posted..." is that bad? What does "...more than an answer..." mean, anyway? I think what I've written addresses the thread topic, or at least raises points related to it.

My last post addressed the post by robprell implying that adding a clustered index would address the ordering concerns. It won't. Did I say something incorrect?

Jeff Mason
Custom Apps, Inc.
[email protected]

Not at all Jeff. I never meant it that way. If I was wrong, please forgive me.

Though we had such a discussion on a similar post in another topic on the ORDER sequence, where I posted a similar query, in this regard I always had that question in my mind why the table was design in such a way, which you have addressed in your last reply. Not referring to robprell's post, in general I referred that your reply had answered the OP's and msa's question. Never meant to add fire to it in a wrong way.

_________________________
- Vijay G
Strive for Perfection