With 4 rows as test input, the number of scans and logical reads are a little bit meaningless. The version with the persisted CTE ColumnValues resulted in 17 scans (1...

Just another idea. Unpivot the columns you want to compare and count the number of ID pairs. At least it makes it easier to add more columns to compare.

Sorry, I thought the comma separated list was a requirement. If you just want a set of (set_id, product_id) replace

SELECT DISTINCT

set_id,

STUFF((

...

I also started with a recursive CTE but it never came to an end. A cursor seems to be reasonably fast in this case. On my laptop your script runs...

You have to test it yourself if it performs better, but here's simplified alternative:

SELECT

Q.memid,

Q.EffectiveDate,

Q.termdate,

...

Ok, here's a more dynamic version:

DECLARE @NumberOfRows INT = 4; -- Must be between 1 and 100

WITH Numbers AS...

Not very dynamic, but here's an attempt. How many rows do you expect for the lowest two dates?

SELECT

ca.n, t.cl_t, t.cl_a,

CASE ca.n

...

According to the 2014 documentation (http://msdn.microsoft.com/en-us/library/dd207007.aspx) this feature was added in SQL 2008. I didn't know of this feature either.

http://msdn.microsoft.com/en-us/library/dd207007(v=sql.105).aspx

If you have BOL installed, just lookup '\' in the index.

You could use a single query like below. I have not tested it against a 4.5 million row input set, but it's probably faster then the loop and all the...

Like this?

SELECT

O.[Locale] [@locale],

(SELECT

I.[Key] as 'ContentId',

I.[Value] as 'Content'

FROM

CreateXML I

...

ChrisM@home (8/6/2013)

Oh Peter, my humblest apology....

Strange, because my solution gives an entirely different result then the solution of Chris

What's wrong with it? Based on the sample data presented, especially

...

11

12

13

13

13

...

I concluded that each number...