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

• This reply was modified 2 years, 1 month ago by  Mark Cowne.
• This reply was modified 2 years, 1 month 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 years, 2 months 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 years, 2 months 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 2 years, 5 months ago by  Mark Cowne.
• This reply was modified 2 years, 5 months ago by  Mark Cowne.
• This reply was modified 2 years, 5 months ago by  Mark Cowne.
• This reply was modified 2 years, 5 months ago by  Mark Cowne. Reason: Problems with square brackets
• This reply was modified 2 years, 5 months 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)...

Try this

declare @MyTable table(Personcode int, XMLBody XML);

insert into @MyTable(Personcode, XMLBody)
values(99999,'<content>
<fieldgroup>
<field id="Comment" type="textbox" title="Comment:" rows="6" cols="50" cssclass="textbox" mandatory="1">Extract the data string located here.</field>
<field id="Action" type="textbox" title="Action:" rows="6" cols="50"...

select t.Id, t.FileName, t.FolderName,
       kp.LicenceNo, kp.IDNumber, kp.Class, kp.CardNumber, kp.ExpiryDate
from MyFiles t
outer apply openjson(t.KeyPairs) 
with (LicenceNo int '$."Licence No "',
  ...

WITH CTE1 AS (
SELECT *,
      CASE WHEN LAG(t_type) OVER(PARTITION BY t_id ORDER BY t_date) = t_type THEN 0 ELSE 1 END AS...

declare @t table(codetype varchar(10), code int, Value int);
insert into @t(codetype, code, Value)
values('Test1',1,2),
('Test1',2,3),
('Test2',4,5),
('Test2',6,7);


SELECT 
   N'{' + 
   STUFF(
      (
...