PiMané (9/23/2013)

---

Yes, had to change it to work...

hunchback (9/22/2013)

---

One way to accomplish this is using a varbinary column to concatenate the sequence of numbers based on the position value.

WITH C1 AS (

SELECT

Id, IdRoot, Name, Position,

CAST(ROW_NUMBER() OVER(ORDER...

You are welcome and thanks for...

Something like this perhaps?

WITH SampleData ([Key], Col1, Col2) AS (

SELECT 1, 'ABC', 'DEF'

UNION ALL SELECT 2, 'GFI', 'XYZ')

SELECT Col1=MAX(CASE WHEN [Key]=Key1 THEN...

The parsing isn't so bad if you use a pattern splitter function and all of your constrains are like this IN type.

WITH CheckConstraint ([definition]) AS (

select...

Does this work for your case?

WITH Orders AS

(

SELECT *

,rn=ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderID DESC)

...

I agree that passing in a subquery for the TYPEd table instead of having to predefine the table would be nice but as you have ascertained, SQL doesn't support it...

Not sure but perhaps this is a simpler approach?

CREATE TABLE #tTable(

sk int NOT NULL IDENTITY UNIQUE CLUSTERED,

CID INT,

CDate DATETIME,

Dept VARCHAR(10)

);

INSERT INTO #tTable(CID, CDate, Dept)

VALUES

(111,'2012-10-05 00:00:00.000','A'),

(111,'2012-10-18 00:00:00.000','C'),

(111,'2012-11-01 00:00:00.000','B'),

(111,'2012-11-01...

Lynn Pettis (9/21/2013)

---

hisakimatama (9/21/2013)

---

Lynn Pettis (9/21/2013)

---

Well, by reading The Thread from start to now you also got to learn some interesting things about many of the regulars here on SSC....

ROW_NUMBER() does not require a PARTITION clause.

Some of the other window aggregates, e.g., MAX(xx), MIN(xx), AVG(xx), do.

You need what I have, which is a vibration...

kevin 32621 (9/19/2013)

---

Well, that's a good question. I'm still relatively new to sql server and all it's nuances, especially dynamic stuff. So I'm not sure how to make this work...

Do you mean something like this?

WITH SampleData (MyDate, StartTime, EndTime) AS (

SELECT '2013-09-20 08:20', '09:00', '11:00'

UNION ALL SELECT '2013-09-20 09:20', '09:00', '11:00'

...

I agree with MM that simply knowing PATINDEX, CHARINDEX and SUBSTRING and playing around a bit, you should be able to construct a working solution.

Another alternative if you are inclined...

A little DDL and sample data would have helped, but in this case it was easy enough to generate.

DECLARE @Contacts TABLE

(

ContactID INT IDENTITY PRIMARY...