DECLARE @t TABLE(col1 VARCHAR(3), col2 DATETIME, col3 INT)

INSERT INTO @t(col1,col2,col3)

SELECT 'us', '01/01/2012', 10 UNION ALL

SELECT 'us', '02/02/2012', 11 UNION ALL

SELECT 'us', '03/03/2012', 33 UNION ALL

SELECT 'gbp', '01/01/2012',...

Here's another way using a hideous xquery

select

P.N.value('.', 'VARCHAR(40)' ) AS TYPEOFPHONE,

P.N.value('for $s in . return ../NUMBER[count(../TYPE[. << $s]) + 1][1]', 'VARCHAR(40)') AS NUMBER

FROM @xMLVAR.nodes('/RECORDS/RECORD') AS...

DECLARE @t TABLE(PNumber VARCHAR(2),ReasonCode VARCHAR(2))

INSERT INTO @t(PNumber,ReasonCode)

SELECT 'P1', 'E1' UNION ALL

SELECT 'P1', 'E1' UNION ALL

--SELECT 'P1', 'D1' UNION ALL

SELECT 'P1', 'D1' UNION ALL

SELECT 'P1', 'D1' UNION ALL

SELECT 'P2', 'E1' UNION...

See if this helps

SELECT Customer_Number,

SUM(Price) As TOTAL,

COUNT(CASE WHEN Product_ID IN...

John Mitchell-245523 (2/8/2012)

---

Precisely. Would you use that...

SQL Kiwi (2/8/2012)

---

Mark-101232 (2/8/2012)

---

The non-deterministic behaviour of UPDATE..FROM is well known and fully documented in BOL. There are other non-deterministic functions in SQL Server such as the ANSI compliant ROW_NUMBER...

The non-deterministic behaviour of UPDATE..FROM is well known and fully documented in BOL. There are other non-deterministic functions in SQL Server such as the ANSI compliant ROW_NUMBER - would you...

SQL Kiwi (2/7/2012)

---

Sample data:

DECLARE @test-2 AS TABLE

(

ID integer PRIMARY KEY,

...

WITH CTE AS (

SELECT part, stat, datevalue,

ROW_NUMBER() OVER(PARTITION BY part ORDER BY datevalue) AS rn

FROM dbo.test_table)

SELECT z.part,z.stat,z.datevalue,n.stat,n.datevalue

FROM CTE z

INNER JOIN CTE n ON...

It's...

I think this gives you all of the combinations you're after, not sure how useful it would be as there's lots of them

WITH CTE(CatID,FieldID,Combinations,OptionsUsed) AS (

SELECT CatID,FieldID,CAST(FieldID AS VARCHAR(1000)),1

FROM #Cat

WHERE...

Use SUBSTRING

SELECT SUBSTRING(Address,1,25) AS AddressPart1,

SUBSTRING(Address,26,25) AS AddressPart2

FROM mytable

CoryJ72 (2/3/2012)

---

Sorry but it does not seem to work. Just to clarify The [Product Code] is the '1001' number and the [Description] is 'Item 1 XX Large'...

WITH CTE AS (

SELECT Product,Code,Description,Cost,

COUNT(*) OVER(PARTITION BY Product,Code) AS cn

FROM Table1

WHERE Description='X Large'

OR (Description='XX Large'...

Ignore ... misread OPs post