Shalinder, you could use a cross-apply cascade, something like this:

SELECT d.TheString,

Col1 = SUBSTRING(TheString,1,p1-1),

Col2 = SUBSTRING(TheString,p1+1,p2-p1-1),

Col3 = SUBSTRING(TheString,p2+1,p3-p2-1),

Col4 = SUBSTRING(TheString,p3+1,p4-p3-1)

FROM (SELECT TheString = 'abcd9efghij9kl9mno', TheDelimiter = '9') d

CROSS APPLY (SELECT...

-- run this and check DateFinished in the output to see how many

-- rows you should expect to return for December 2012, the current month.

-- The oldest rows...

They can...

Can you post your original query, George? The last two weeks' version? Cheers.

Use a cross-tab query to pivot the results, it's generally faster than PIVOT and more intuitive too:

SELECT

Col1 = MAX(CASE WHEN ItemNumber = 1 THEN Item END),

Col2 = MAX(CASE WHEN...

Randy Doub (11/29/2012)

---

In Access, if I query sql server and build a local table of something like 30,000 id numbers, then join that local table to a linked sql server...

This gets my vote, by...

The optimizer struggles with chained CTE's where elements in the chain are at different levels of aggregation, which is exactly what you have here. Try running the results into a...

-- sample data

;WITH SampleData (StockCode, supplydate, AvailableQty) AS (

SELECT 'a', '01/12/12', 10 UNION ALL

SELECT 'a', '08/12/12', 2 UNION ALL

SELECT 'a', '28/12/12', 6 UNION ALL

SELECT 'a', '5/01/13', 15 UNION ALL

SELECT 'a',...

Create a unique clustered index on column [row].

Jeff Moden (11/19/2012)

---

ChrisM@home (10/15/2012)

---

Good point, Jan. I remember recently Howard W posted evidence...

Sony Francis @EY (11/7/2012)

---

Try this

SELECT *

FROM TableA a

LEFT OUTER JOIN TableB b ON a.LoanID = b.LoanID

WHERE b.ColumnA <> 'Y'

When we use INNER JOIN it will consider only commom records....

-- Have a look at the dupesets in the source table - do you want them, or not?

-- Then decide what to do. No point in finding a clever way...

+1

Is this structure the same or different for each string of numbers you have to process?