Viewing 15 posts - 31 through 45 (of 1,439 total)
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
WITH Ranges AS (
SELECT (RecordId - NumDays + 1) As Start, RecordId
FROM #test
WHERE NumDays > 0)
UPDATE #test SET AmountAll = B.AmountAll
FROM #test A
JOIN (SELECT r.RecordId, SUM(t.Amount)...
August 18, 2021 at 8:50 am
This should work for you
select x.r.value('Index[1]','int') as ChannelID,
x.r.value('(SensorScaling/OutUnits)[1]','varchar(20)') as [SI Units]
from @DataSheetXML.nodes('Datasheet/ChannelList/ChannelDefinition[@IsEnableable="True"]') x(r);
July 20, 2021 at 2:50 pm
See if this helps
select pd.a.value('let $a := . return 1 + count(../ProbeDefinition[. << $a])', 'int') AS MasterRecordNumber,
cl.b.value('.','int') as ChannelListValue
from @DataSheetXML.nodes('Datasheet/ProbeList/ProbeDefinition')...
July 9, 2021 at 2:47 pm
with cte as (
select ID, TS, FLAG,
case when lag(flag) over(partition by id order by ID,TS) = flag then 0 else...
June 10, 2021 at 8:48 am
SELECT m.displayid, STRING_AGG(m.promo_id,';') AS Concat_Promo_Id, STRING_AGG(h.promogroup,';') AS Concat_promogroup
FROM Product_Main m
LEFT OUTER JOIN Product_Helper h ON h.displayid = m.displayid AND h.promo_id = m.promo_id
GROUP BY m.displayid;
June 3, 2021 at 7:53 am
Replace
Data.Col.value('(./PartyID)[1]', 'int') As Party_ID
with
Data.Col.value('(../PartyID)[1]', 'int') As Party_ID
May 3, 2021 at 2:35 pm
select t1.PartFamilyId, t1.FamilyStatus + isnull('|' + ca.FamilyStatus,'') as FamilyStatus
from #partsFamily t1
outer apply (select 'NULL' from #partsFamily t2 where t2.PartFamilyId = t1.PartFamilyId and t2.FamilyStatus is null) ca(FamilyStatus)
where t1.FamilyStatus...
April 28, 2021 at 1:00 pm
Here's another way to do gaps and islands
WITH C1 AS (
SELECT id, category, StartDate,
CASE WHEN LAG(category) OVER(PARTITION BY id ORDER BY StartDate) = category THEN 0...
April 27, 2021 at 3:38 pm
Viewing 15 posts - 31 through 45 (of 1,439 total)
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy