I can certainly empathize with your plight, having worked in the dank dungeon of SQL 2000 for some time. The following will work for you there:

DECLARE @docs TABLE

(clientkey INT,...

DDL=Data Definition Language

That and the INSERT to set up your sample data looks like this:

DECLARE @sample TABLE (id int identity(1,1), s_date datetime, amount int)

insert into @sample

select '2010-01-01', 1

union all select...

First of all Jacques, welcome to the SSC forum!

I'm not 100% sure exactly which rows you want to return so the query below returns 3 sets (separated by UNION ALL):

1....

Lynn - Oops! You are right.

Lynn's query returns only one row:

DCODDName

J6598JOHN

Which may be what you want. However if you want all of JOHN's records, you can use this:

DECLARE @t TABLE

(DCOD CHAR(5), DNAME VARCHAR(10),...

GSquared,

I see no flaws in your comparison. Clearly the UPDATE version of yours is faster than the original one you posted with only a SELECT. Too bad I...

After all was said and done, I decided to add the famous 1000000 row test harness (only with 100000 rows though), so I came up with the following two tests...

GSquared,

It is good to know it when someone uses a posted solution in a production environment because it at least confirms that it probably works pretty well (i.e., no bugs).

The...

GSquared,

Thanks for pointing out an issue with the ampersand REPLACE I was doing. I corrected my post accordingly.

Now as to performance, I'm curious why you think the version you...

At this point, I'd say scrap my suggestions and go with your original.

Can't always be right.

sbuchan - You're most welcome.

Gotta give credit where credit is due though. My first attempt at a solution didn't look as good as Sean's so it was his solution...

To use my solution you just need to remove the top 2 CTEs (that I took from Sean) and that you say you removed to use his solutions. You'll...

Mark,

I didn't particularly like my solution but thought that it worked.

Looked like the OPs expected result set, even sorted right through mutliple machinations.

Didn't have access to SQL 2008 when I...

Here's an alternate to Sean's solution that appears to have a better query plan.

;WITH flight_tran_table (flight_tran_id , OtherData)

AS

(

SELECT 1234, '(Other Tran Data)'

),

flight_sectors_table(tran_id,sector_id,dept,arrive)

AS

(

SELECT 1234,1,'MAN','LHR' UNION ALL

SELECT 1234,2,'LHR','JFK' UNION ALL

SELECT 1234,3,'JFK','LHR' UNION...

The SQL-Jedi of this site will no doubt admonish you to let the presentation layer deal with this kind of sorting issue.

However since I am but a mere Paduan, I...