Lots of options. For your temporary table solution you can do this

WITH CTE AS (

SELECT date_year,

date_and_time,

...

Per year as below. As for not using CTEs, you could change the CTE to a derived table, but that's pretty much just a cosmetic difference.

WITH CTE AS (

SELECT...

You can use this to give you output 2. Output 1 is where Rank is 1

WITH CTE AS (

SELECT date_and_time,

ROW_NUMBER() OVER(ORDER...

Here's another way. For any comma, count the number of double quotes preceeding it. If there is an even number count it, otherwise ignore.

WITH CTE AS (

SELECT t.id,

...

See if this helps

DECLARE @t TABLE(xmlCol NTEXT)

INSERT INTO @t(xmlCol)

VALUES('<NewDataSet>

<XmlLink>

<ObjectId>123456</ObjectId>

<ObjectName>Jo Bloggs</ObjectName> ...

Have a look here

http://www.sqlservercentral.com/articles/IsNumeric/71512/

Have a look here

http://www.sqlservercentral.com/Forums/Topic1248957-392-1.aspx

I think it simplifies to this

SELECT

startdate

,enddate

,descript

FROM testdates

WHERE @End >= startdate AND @Start<=enddate

ORDER BY startdate, enddate

WITH CTE AS (

SELECT ID, peter, mike, steve,

ROW_NUMBER() OVER(PARTITION BY Peter ORDER BY Mike DESC) AS rn

FROM dbo.ATable)

SELECT peter, mike,...

Using my query from here

http://www.sqlservercentral.com/Forums/Topic1125847-392-1.aspx

WITH StartsAndEnds(StartEnd,StartDate,EndDate,CustomerID,ServiceType) AS (

SELECT 'S' AS StartEnd,

StartDate,

DATEADD(day,-1,StartDate),

...

DECLARE @t TABLE(X CHAR(1), Y CHAR(1))

INSERT INTO @t(X,Y)

SELECT 'A' ,'B' UNION ALL

SELECT 'A' ,'B' UNION ALL

SELECT 'A' ,'C' UNION ALL

SELECT 'A' ,'C' UNION ALL

SELECT 'A' ,'C' UNION ALL

SELECT 'A' ,'D'...

dwain.c (2/20/2012)

---

Jeff - Thanks for pointing that out. I was a bit rushed (had to run off to a meeting) so I didn't carefully check the result set.

However the...

Have a look here

http://www.sqlservercentral.com/articles/comma+separated+list/71700/

Gianluca Sartori (2/13/2012)

---

I don't think such a syntax exists. I'm afraid...

You're better off asking here

http://www.sqlservercentral.com/Forums/Forum131-1.aspx