Literally all of the original question starting with "I have...

Just for fun I tried cutting and pasting the original question into ChatGPT, this is what it came up with. As far as I can tell it works.

You can use...

Something like this?

WITH GroupedNational_Identity AS (
SELECT uniqueid, natid_type, STRING_AGG(natid_num,'|') AS natid_num, active, individual_list_id_fk
FROM national_identity_test
GROUP BY uniqueid, natid_type, active, individual_list_id_fk
)
SELECT 'CNIC' as ID_TYPE_01,
 MAX(CASE WHEN NatID_Type = 'CNIC'...

Use ISNULL

SELECT ISNULL(
(SELECT 'CNIC' as ID_TYPE_01,
       MAX(CASE WHEN NatID_Type = 'CNIC' THEN NatID_Num END) AS ID_NUMBER_01,
   'PASSPORT' as ID_TYPE_02,
...

This should work for you

SELECT 'CNIC' as ID_TYPE_01,
       MAX(CASE WHEN NatID_Type = 'CNIC' THEN NatID_Num END) AS ID_NUMBER_01,
   'PASSPORT'...

Duplicate deleted

• This reply was modified 1 year, 5 months ago by  Mark Cowne.

Try this

s.PO.value('(/UserArea/Property/NameValue[@name="ManufacturerPart"])[1]', 'nvarchar(50)') as MFG

I'm not really clear about how the data fits together, but this query matches your expected results.

This is for a fixed number of approaches, if you want this to be...

This should handle multiple channels

SELECT device.DeviceSerialNumber,Readings.ChannelId,Readings.DataHashDto,
       channel_list.Si,channel_list.Raw,channel_list.Conversion,channel_list.TimeStamp
FROM OPENJSON(@json)
WITH
(
ChannelReadings NVARCHAR(MAX) '$.ChannelReadings' AS JSON,
DeviceSerialNumber NVARCHAR(100) '$.DeviceSerialNumber'
) AS device
OUTER APPLY OPENJSON(device.ChannelReadings)
WITH
(
ChannelId INT '$.ChannelId',
DataHashDto NVARCHAR(MAX)...

Just remove the 'order by'

select t.* from (
select a.Dept,SUM(t.Amount) AS Amount
from TableA a
outer apply(select top 1 b.Amount
          ...

Try this

select a.Dept,SUM(t.Amount) AS Amount
from TableA a
outer apply(select top 1 b.Amount
            from TableB b
where b.CustNo =...

select a.CustNo,t.Amount
from TableA a
outer apply(select top 1 b.Amount
            from TableB b
where b.CustNo = a.CustNo
order by abs(datediff(day,a.[Date],b.[Date])))...

declare @mytable table(mydata nvarchar(max));

insert into @mytable(mydata)
values(N'
{
"_Journals": [
{
"SourceType": 18,
"JournalID": "sdfdsf-fdfs-dfdf-dfdfd-dfdsfsfdd",
"SourceID": "sdfsdf-sdfds-sdf-dfd-sdfsdf",
"JournalLines": [
{
"AccountType": 3,
"JournalLineID": "sdfsdfsd-sdfsdf-sdfsd-sdfsdf-sdfsdf",
"TaxName": "Some tax name",
"TrackingCategories": []
},
{
"AccountType": 3,
"JournalLineID": "sdfsdfsdf-sdfsdf-sdf-sdf-sdf",
"TaxName": "text",
"TrackingCategories": []
}
]
},
{
"SourceType": 18,
"JournalID": "sdfsd-sdfd-sdfd-sdf-dfdsfd",
"SourceID": "61da35a7-4f8c-4d3a-9a18-e531734b419c",
"JournalLines": [
{
"AccountType": 3,
"JournalLineID": "sdfdsf-dsfds-sdf-sdfsd-sdfd",
"TaxName":...

This, I think

SELECT 'Class1' AS "@ClassName",
       'String' AS "ExternalId/@TYPE",
   AccountNumber AS "ExternalId"
FROM #Account
FOR XML PATH('Element'),TYPE;

Try this

with cte as (
select AccountId,Values_1,Time_1,Designation,
       lag(Values_1) over(partition by AccountId order by Time_1) as PrevValues_1
from DataTable
)
select AccountId,Values_1,PrevValues_1,Time_1,Designation,abs(Values_1 - PrevValues_1) as Diff
from...