Maybe this?

WITH CTE AS (
SELECT t.id,
       j2.[key] AS field,
   j2.value AS val,
   row_number() over(partition by j2.[key] order...

Sure, there's plenty of good folks here willing to help.

You're welcome! Thanks for the feedback.

This is close to what you want

with cte as (

select OptionsetName,

LocalizedLabel,

row_number()over(partition by optionsetname order by cast([option]...

DENSE_RANK() OVER(ORDER BY Id - register_sequence) AS seq_number

• This reply was modified 1 month, 2 weeks ago by  Mark Cowne.
• This reply was modified 1 month, 2 weeks ago by  Mark Cowne.

Small tweak to Phils code should do it

SELECT
    q1.success
,   q1.timestamp
,   q1.date
,   q1.base
,   q1.unit
,   q2.[key]...

• This reply was modified 2 months, 1 week ago by  Mark Cowne.

SELECT id,
       MIN(Game_Time) AS Game_Start,
       MAX(Game_Time) AS Game_END
FROM Game
GROUP BY id
ORDER BY id;

• This reply was modified 2 months, 3 weeks ago by  Mark Cowne.

SELECT Date,ID,CHAR(ASCII('A') + (ROW_NUMBER() OVER(PARTITION BY Date ORDER BY ID) -1) % 3) AS Grp
FROM #s
ORDER BY Date,ID;

@shogunSQL, this should work for you.

SELECT C.x.value('@Id', 'varchar(20)') as Id 
,C.x.value('@ChapterName', 'varchar(20)') as ChapterName
,C.x.value('title[1]', 'varchar(100)') as Title
,C.x.value('concat(local-name(parent::*/parent::*),"\",local-name(parent::*),"\",local-name(.)) ', 'VARCHAR(1000)') AS [Path]
FROM @doc.nodes('ROOT/*/*/item') as C(x)

Another way

SELECT C.x.value('@Id', 'varchar(20)') as Id 
,C.x.value('@ChapterName', 'varchar(20)') as ChapterName
,C.x.value('concat(local-name(parent::*/parent::*),"\",local-name(parent::*),"\",local-name(.)) ', 'VARCHAR(1000)') AS [Path]
FROM @doc.nodes('ROOT/*/*/item') as C(x)

Deleted, wrong solution

• This reply was modified 5 months, 2 weeks ago by  Mark Cowne.
• This reply was modified 5 months, 2 weeks ago by  Mark Cowne.
• This reply was modified 5 months, 2 weeks ago by  Mark Cowne.
• This reply was modified 5 months, 2 weeks ago by  Mark Cowne. Reason: Problems with square brackets
• This reply was modified 5 months, 2 weeks ago by  Mark Cowne.

Not totally clear to me what you're asking for, maybe this?

with cte as (
select REF_NO,
       n.x.value('Country[1]','varchar(30)') as Country,
   ...

This should work pre 2016

select stuff((select ',' + n.x.value('Country[1]','varchar(30)') as "text()"
from MyView t
cross apply t.GlobalCountryRegionXML.nodes('/GlobalCountryRegion/CountryRegion') n(x) 
order by row_number() over(order by n.x)
for xml path('')),1,1,'') as Country,
stuff((select ','...

with cte as (
select n.x.value('Country[1]','varchar(30)') as Country,
       n.x.value('Region[1]','varchar(30)') as Region,
   row_number() over(order by n.x) as rn
from MyView t
cross apply...

with cte as (
select TestType, TestDate, case when lag(TestType) over(order by TestDate) = TestType then 0 else 1 end as IsStart
from #T
)
select TestType, TestDate,sum(IsStart) over(order by TestDate)...