Sorry, but from reading your text description, I have no idea what you are trying to accomplish. Could you please show some examples?

Here is the query to give you the results you want.

I assume you can write the stored procedure to pass in a parameter to a WHERE clause.

;with...

Is there an upper limit to how many descriptions might have to be concatenated?

If so, how many? 10? 20? 100? 1000?

Glad it's working for you. I just hope you don't have too many rows in these user tables, because that is NOT going to scale well.

You're welcome. I'm sure you'll find enough to get you started, but don't hesitate to come back if you have specific questions.

By the way, PIVOT is an...

Sorry to take so long. My daughter called from NZ and she gets priority 🙂

Anyway, I think this will give you the results you want. Use...

Coming right up.

But the IDs should remain the same, only the sort value will change on each row. Right?

I think I understand the problem now. He wants the [Sort] columns for all rows with a number equal to or greater to the "moved" row to be...

What troubles me is the idea that you are trying to store rows in a specific order in a table. This isn't a spreadsheet or a flat file,...

The output you want is commonly referred to as a PIVOT or cross-tab ("crosstab"). If you'll search this site using those terms, you'll find plenty of examples....

:crazy:

We need some use cases. Please post up what your current source table(s) look like and the query as it has been developed so far. ...

It helps to remember that CASE is an expression, not a statement.

CASE is basically like a function with "if logic" built in, a more powerful version of ISNULL() or COALESCE().

drew.allen (11/9/2011)

---

The other option is to use the OUTPUT clause to return ALL of the new IDs rather than just the LAST new ID that you would get with scope...