Viewing 15 posts - 1 through 15 (of 1,416 total)
DENSE_RANK() OVER(ORDER BY Id - register_sequence) AS seq_number
May 19, 2022 at 10:59 am
Small tweak to Phils code should do it
SELECT
q1.success
, q1.timestamp
, q1.date
, q1.base
, q1.unit
, q2.[key]...
April 27, 2022 at 1:23 pm
SELECT id,
MIN(Game_Time) AS Game_Start,
MAX(Game_Time) AS Game_END
FROM Game
GROUP BY id
ORDER BY id;
April 12, 2022 at 7:42 am
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;
March 18, 2022 at 5:17 pm
@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)
March 1, 2022 at 10:47 am
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)
February 25, 2022 at 4:00 pm
Deleted, wrong solution
January 20, 2022 at 1:36 pm
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,
...
October 22, 2021 at 10:49 am
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 ','...
October 21, 2021 at 4:42 pm
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...
October 21, 2021 at 4:04 pm
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)...
October 5, 2021 at 1:44 pm
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"...
September 17, 2021 at 10:18 am
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 "',
...
September 16, 2021 at 9:10 am
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...
August 23, 2021 at 7:55 pm
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(
(
...
August 18, 2021 at 2:44 pm
Viewing 15 posts - 1 through 15 (of 1,416 total)