My solution for what it's worth 😉

SELECTIDENTITY(INT,1,1) AS [RowNum],

PartNumber,

Factor

INTO#MyHead

FROMdbo.XlsDump

ORDER BY PartNumber, Factor

SELECT IDENTITY(INT,0,1) AS [GroupNumber],0 AS [RowNum]

INTO #MyHead2

INSERT #MyHead2 (RowNum)

SELECT a.RowNum

FROM #MyHead a

LEFT JOIN #MyHead n ON n.RowNum=a.RowNum+1

WHERE NOT (a.Factor=n.Factor)

OR...

SQLkiwi (3/7/2011)

---

Gianluca Sartori (3/7/2011)

---

Great job, Paul! Outstanding, as usual.

Now there's a chance that a poor n00b like me can...

Ian Scarlett (2/24/2011)

---

OMG, that makes me feel...

oecus

GSquared (2/18/2011)

---

David Burrows (2/18/2011)

---

string strsql = "";

strsql += " Select '' AS [CustomerId],' ---Select Customer --- ' AS [CompanyName] ";

strsql += " Union Select CustomerId, CompanyName ";

strsql +=...

Gianluca Sartori (2/18/2011)

---

I have to admit it was my fault.

If I didn't post on THE THREAD a "Lennie alert" nobody would have noticed, Lennie would have got his answer and...

string strsql = "";

strsql += " Select '' AS [CustomerId],' ---Select Customer --- ' AS [CompanyName] ";

strsql += " Union Select CustomerId, CompanyName ";

strsql += " From testCustomers...

Mine says it all :w00t:

Or even this

SELECT RegionName,

AVG(CASE WHEN Month_Value = 7 THEN Answer END) AS [PreviousMonthAns],

AVG(CASE WHEN Month_Value = 8 THEN Answer END) AS [Answer]

FROM Mst_Location A

JOIN Voc_Trans_Details D...

This probably better than the join

SELECT RegionName,

CASE WHEN Month_Value = 7 THEN Answer END AS [PreviousMonthAns],

CASE WHEN Month_Value = 8 THEN Answer END AS [Answer]

FROM x

GROUP...

WITH x (RegionName, Month_Value, Answer) AS (

SELECT B.RegionName, D.Month_Value, AVG(D.Answer) AS [Answer]

FROM Mst_Location A

JOIN Voc_Trans_Details D ON A.LocationID = D.Location_Id

JOIN Master_Map_RegionWithLocation C ON A.LocationID = C.LocationID

JOIN...

My query had a problem and for the record I post a revised version

Based on a million rows of my test data

'1,2,3,"abc,def",4,5,"ghi,jkl",6,7,"8,"""",x,y"""",",9'

on my desktop, it took 7 iterations taking...

This solution uses a temp table to add a unique id to the data

It needs to be repeated until no updates are left

(sorry about the short names but it was...

Craig Farrell (1/24/2011)

---

Try swapping:

PatIndex('%"%,%"%',THE_LINE)),

-- with this:

PatIndex('%,"%,%",%',THE_LINE)),

Note where the additional commas are to detect beginning and ending doublequotes.

I may have an alternative for you but it's going to be a busy...

This will replace commas between quotes with the pipe character

STUFF([column],CHARINDEX(',',[column],PATINDEX('%"%,%"%',[column])),1,'|')...