SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE AA_BILL_DETAILS_Property (
  "ARRANGEMENT_ID" VARCHAR(12),
  "REPAY_AMOUNT" NVARCHAR(15),
  "REPAY_REF" NVARCHAR(63),
  PAY_PROPERTY NVARCHAR(63)
);

INSERT INTO AA_BILL_DETAILS_Property
  ("ARRANGEMENT_ID", "REPAY_AMOUNT", "REPAY_REF", PAY_PROPERTY)
VALUES
  ('AA21084T517V', N'3678.412050.25', N'AAACT223212XPWZDCY-20221117AAACT222933SXX4KLB-20221020', N'ACCOUNTPRINCIPALINT'),
  ('AA21084T517V', N'1931.41931.4', N'AAACT22059QK5XTXSD-SUSPEND-20220228AAACT22059QK5XTXSD-20220228', NULL),
  ('AA21084T517V', NULL, NULL, NULL);
3 rows affected

-- Original Query
SELECT
        ARRANGEMENT_ID,
        REPAY_AMOUNT,
        REPAY_REF,
        CAST(RIGHT(RR.Value,8) AS DATE) 'Split Date',
        RA.Value 'Split Amt'
        
FROM    AA_BILL_DETAILS_Property

        CROSS APPLY OPENJSON(CONCAT('["', REPLACE(REPAY_REF, N'', '","'), '"]')) RR
        CROSS APPLY OPENJSON(CONCAT('["', REPLACE(REPAY_AMOUNT, N'', '","'), '"]')) RA

WHERE   RR.[key] = RA.[key]
ORDER BY    [Split Date] DESC


ARRANGEMENT_ID
REPAY_AMOUNT
REPAY_REF
Split Date
Split Amt


AA21084T517V
3678.412050.25
AAACT223212XPWZDCY-20221117AAACT222933SXX4KLB-20221020
2022-11-17
3678.41

AA21084T517V
3678.412050.25
AAACT223212XPWZDCY-20221117AAACT222933SXX4KLB-20221020
2022-10-20
2050.25

AA21084T517V
1931.41931.4
AAACT22059QK5XTXSD-SUSPEND-20220228AAACT22059QK5XTXSD-20220228
2022-02-28
1931.4

AA21084T517V
1931.41931.4
AAACT22059QK5XTXSD-SUSPEND-20220228AAACT22059QK5XTXSD-20220228
2022-02-28
1931.4

AA21084T517V
null
null
1900-01-01



SELECT
        ARRANGEMENT_ID,
        REPAY_AMOUNT,
        REPAY_REF,
        CAST(RIGHT(RR.Value,8) AS DATE) [Split Date],
        RA.Value [Split Amt],
        PAY_PROPERTY,
        PP.Value PP

FROM    AA_BILL_DETAILS_Property
CROSS APPLY OPENJSON(CONCAT('["', REPLACE(REPAY_REF, N'', '","'), '"]')) RR
CROSS APPLY OPENJSON(CONCAT('["', REPLACE(REPAY_AMOUNT, N'', '","'), '"]')) RA
OUTER APPLY (
    SELECT *
    FROM OPENJSON(CONCAT('["', REPLACE(PAY_PROPERTY, N'', '","'), '"]')) PP
    WHERE RR.[key] = PP.[key]
) PP
WHERE   RR.[key] = RA.[key]
ORDER BY
    [Split Date] DESC


ARRANGEMENT_ID
REPAY_AMOUNT
REPAY_REF
Split Date
Split Amt
PAY_PROPERTY
PP


AA21084T517V
3678.412050.25
AAACT223212XPWZDCY-20221117AAACT222933SXX4KLB-20221020
2022-11-17
3678.41
ACCOUNTPRINCIPALINT
ACCOUNT

AA21084T517V
3678.412050.25
AAACT223212XPWZDCY-20221117AAACT222933SXX4KLB-20221020
2022-10-20
2050.25
ACCOUNTPRINCIPALINT
PRINCIPALINT

AA21084T517V
1931.41931.4
AAACT22059QK5XTXSD-SUSPEND-20220228AAACT22059QK5XTXSD-20220228
2022-02-28
1931.4
null


AA21084T517V
1931.41931.4
AAACT22059QK5XTXSD-SUSPEND-20220228AAACT22059QK5XTXSD-20220228
2022-02-28
1931.4
null
null

AA21084T517V
null
null
1900-01-01

null



SELECT
        ARRANGEMENT_ID,
        REPAY_AMOUNT,
        REPAY_REF,
        PAY_PROPERTY,
        v.*
FROM    AA_BILL_DETAILS_Property
OUTER APPLY (
    SELECT
        CAST(RIGHT(RR.Value,8) AS DATE) [Split Date],
        RA.Value [Split Amt],
        PP.Value PP
    FROM OPENJSON(CONCAT('["', REPLACE(REPAY_REF, N'', '","'), '"]')) RR
    FULL JOIN OPENJSON(CONCAT('["', REPLACE(REPAY_AMOUNT, N'', '","'), '"]')) RA
        ON RR.[key] = RA.[key]
    FULL JOIN OPENJSON(CONCAT('["', REPLACE(PAY_PROPERTY, N'', '","'), '"]')) PP
        ON ISNULL(RA.[key], RR.[key]) = PP.[key]
) v
ORDER BY
    [Split Date] DESC


ARRANGEMENT_ID
REPAY_AMOUNT
REPAY_REF
PAY_PROPERTY
Split Date
Split Amt
PP


AA21084T517V
3678.412050.25
AAACT223212XPWZDCY-20221117AAACT222933SXX4KLB-20221020
ACCOUNTPRINCIPALINT
2022-11-17
3678.41
ACCOUNT

AA21084T517V
3678.412050.25
AAACT223212XPWZDCY-20221117AAACT222933SXX4KLB-20221020
ACCOUNTPRINCIPALINT
2022-10-20
2050.25
PRINCIPALINT

AA21084T517V
1931.41931.4
AAACT22059QK5XTXSD-SUSPEND-20220228AAACT22059QK5XTXSD-20220228
null
2022-02-28
1931.4


AA21084T517V
1931.41931.4
AAACT22059QK5XTXSD-SUSPEND-20220228AAACT22059QK5XTXSD-20220228
null
2022-02-28
1931.4
null

AA21084T517V
null
null
null
1900-01-01

ARRANGEMENT_ID	REPAY_AMOUNT	REPAY_REF	Split Date	Split Amt
AA21084T517V	3678.412050.25	AAACT223212XPWZDCY-20221117AAACT222933SXX4KLB-20221020	2022-11-17	3678.41
AA21084T517V	3678.412050.25	AAACT223212XPWZDCY-20221117AAACT222933SXX4KLB-20221020	2022-10-20	2050.25
AA21084T517V	1931.41931.4	AAACT22059QK5XTXSD-SUSPEND-20220228AAACT22059QK5XTXSD-20220228	2022-02-28	1931.4
AA21084T517V	1931.41931.4	AAACT22059QK5XTXSD-SUSPEND-20220228AAACT22059QK5XTXSD-20220228	2022-02-28	1931.4
AA21084T517V	null	null	1900-01-01