Try LEFT JOINing [ODS].[AC_Snapshot] again (with a different alias).

Teach a guy to fish - I'd like to add that...

Closer, but still no cigar:

SELECT

RTRIM(First_name) + ' ' + RTRIM(Last_Name),

RTRIM(First_name) + CASE WHEN RIGHT(First_name,1) = ' ' THEN ' ' ELSE '' END + RTRIM(Last_Name),

...

Ninja's_RGR'us (10/14/2011)

---

Post...

;WITH tree AS (

SELECT DISTINCT uid, cat_parent, cat_name, node = 0

FROM #temp where cat_parent = 0

UNION ALL

SELECT t.uid, t.cat_parent, t.cat_name, node = l.node+1

FROM tree L

INNER JOIN #temp t ON l.uid...

20x faster! I'm going home...:blush:

Anytime Phil. Test them both, I reckon Lowell's MegaCASE will win hands down.

Lowell (10/14/2011)

---

maybe it's just me, but both my solution and Chris's seem to produce the exact same execution plan?, but Chris's is 48% relative to the batch where mine...

Something similar to this outperforms the obvious pivot approach for removing the blanks from address elements:

select t.*, x.ColIndex

from @Tab1 t

CROSS APPLY (SELECT TOP 1 ColIndex from (

SELECT ColIndex =...

Can you confirm which version of SQL Server you are using? There's no 2003 version.

Easy - test them 😉

It's an unrestricted update though, one pass is likely to be better than two or more.

No problem. This relies heavily on the testing framework which Jeff Moden et al set up for his excellent string splitter article[/url].

Here's the rCTE code:

CREATE FUNCTION [dbo].[DS8K_rCTEa](@pString [varchar](8000), @pDelimiter [char](1))

RETURNS...

codebyo (10/13/2011)

---

You could try running a test example using the estimated execution plan button and the percentage taken by each method. I can tell you that the single UPDATE affecting...

Bearing in mind that you are aggregating quite a few columns from a million or so rows down to 140,000, this query will always take some time to run. However,...

Good catch, Drew. I was about to point it out with evidence:

USE tempdb

GO

CREATE TABLE tblUCSU (strlocation VARCHAR(10), strPen INT)

INSERT INTO tblUCSU (strlocation, strPen)

SELECT 'London', 1 UNION ALL

SELECT 'New York',...