please provide the sample xml data in total, not only the first line.

You'll need to use WITH XMLNAMESPACES() together with your select statement, and maybe you'd need to add the...

Glad I could help 😀

Would you mind sharing the performance difference for the solution you used before vs. the one I provided? Just being curious...

Please provide table def and sample data so we have something to test against.

Also, please show us what you've tried so far.

ColdCoffee (8/10/2010)

---

Not at all, Lutz. I already coded the sample data with IDENTITY column , so that i will get a ROW...

With your current design it's close to impossible (unless you're going to use some delimited strings): you have two variables (@SortBy and @SortDirection) and you want to have more than...

Even though I have no idea what value you want to store in the DescriptionID column nor do I have a clue as how you'd calculate the number to be...

GilaMonster (8/10/2010)

---

...Love to, but it wouldn't be allowed through customs on this side. Same with honey, fruit and meat products etc. Otherwise I'd bring biltong when I visit (as...

Drew,

I guess the Row_Number approach I was hitting for was overdesigned for the task...

You're absolutely right, for the given task a simple join on the time columns would be enough.

That's...

You would need to use ROW_NUMBER() to put your data in sequence and do a self join on this subquery (or better, CTE) based on the row number column with...

I would use CrossTab over PIVOT due to more flexibility:

;

WITH cte AS

(

SELECT

RP.PERIODNAME,

[1.1] = COUNT(CASE WHEN C.CASETYPE = 'F' AND C.PROPERTYTYPE = 'P' AND RIGHT(C.IRN, 2) = 'PA' AND CE.EVENTNO =...

PIVOT isn't really that great of a tool if you either have to pivot by more than one column or if your target columns aren't fixed.

You should have a look...

:-D;-):-D

I don't think that's actually true: That's how we get rid of fruit flies:

1/2 shot glass cider vinegar,

1/2 shot...

Please have a look at the TallyTable article referenced in my signature.

Once you have such a table it's as easy as

SELECT @start + N

FROM Tally

WHERE N <= (@end - @start)

(You...

I would probably use PATINDEX(). Something like

DECLARE @search CHAR(2)

SET @search='sm';

WITH tab AS

(

SELECT 'Smith' AS lastname, 'John' AS firstname UNION ALL

SELECT 'GoldSmith', 'Vincent' UNION ALL

SELECT 'Smith', 'Patrick' UNION ALL

SELECT 'Blacksmith',...

Would something along the following lines help you? (the "trick" is to build subgroups by contract_type as shown with the subgrp aliased column)

;

WITH cte AS

(

SELECT

*,

ROW_NUMBER() OVER(ORDER BY...