SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE md (
        laborOfficeId NVARCHAR(2),
        sequenceNumber NVARCHAR(12),
        unifiedNationalNumber NVARCHAR(MAX),
        nationalId NVARCHAR(MAX) not null,
        paidStatus NVARCHAR(3) not null, -- Assuming Yes/No as values
        bankName NVARCHAR(MAX),
        Iban NVARCHAR(MAX),
        WPSalary NVARCHAR(MAX), -- Assuming it's a decimal value
        paidYearMonth CHAR(6) not null, -- Assuming the format is fixed as YYYYMM
        deductionAmount NVARCHAR(MAX), -- Assuming it's a decimal value
        deduction NVARCHAR(MAX)
    );
   
    INSERT INTO md (laborOfficeId, sequenceNumber, unifiedNationalNumber, nationalId, paidStatus, bankName, Iban, WPSalary, paidYearMonth, deductionAmount, deduction)
    VALUES 
    ('1', '123456', '70023455436', '1143575354', 'yes', 'alrajhi', 'SA4420000422608010555321', '5000.00', '202401', '73.00', 'Absents')
    ,
    ('1', '123456', '70023455436', '1143575354', 'yes', 'alrajhi', 'SA4420000422608010555321', '5000.00', '202402', '73.00', 'Absents')
    ,
    ('1', '123456', '70023455436', '1143575354', 'yes', 'alrajhi', 'SA4420000422608010555321', '5000.00', '202403', '73.00', 'Absents')
    ;
3 rows affected

SELECT * FROM md


laborOfficeId
sequenceNumber
unifiedNationalNumber
nationalId
paidStatus
bankName
Iban
WPSalary
paidYearMonth
deductionAmount
deduction


1
123456
70023455436
1143575354
yes
alrajhi
SA4420000422608010555321
5000.00
202401
73.00
Absents

1
123456
70023455436
1143575354
yes
alrajhi
SA4420000422608010555321
5000.00
202402
73.00
Absents

1
123456
70023455436
1143575354
yes
alrajhi
SA4420000422608010555321
5000.00
202403
73.00
Absents


SELECT 
    laborOfficeId,
    sequenceNumber,
    unifiedNationalNumber,
    nationalId,
    (SELECT 
        paidStatus, 
        bankName,
        Iban,
        WPSalary,
        paidYearMonth,
        deductionAmount,
        deduction 
     FROM md AS md2
     WHERE md2.laborOfficeId = md.laborOfficeId 
       AND md2.sequenceNumber = md.sequenceNumber 
       AND md2.unifiedNationalNumber = md.unifiedNationalNumber 
       AND md2.nationalId = md.nationalId
     FOR JSON PATH) AS paidStatusList
FROM md
GROUP BY laborOfficeId, sequenceNumber, unifiedNationalNumber, nationalId;


laborOfficeId
sequenceNumber
unifiedNationalNumber
nationalId
paidStatusList


1
123456
70023455436
1143575354
[{"paidStatus":"yes","bankName":"alrajhi","Iban":"SA4420000422608010555321","WPSalary":"5000.00","paidYearMonth":"202401","deductionAmount":"73.00","deduction":"Absents"},{"paidStatus":"yes","bankName":"alrajhi","Iban":"SA4420000422608010555321","WPSalary":"5000.00","paidYearMonth":"202402","deductionAmount":"73.00","deduction":"Absents"},{"paidStatus":"yes","bankName":"alrajhi","Iban":"SA4420000422608010555321","WPSalary":"5000.00","paidYearMonth":"202403","deductionAmount":"73.00","deduction":"Absents"}]


SELECT 
    laborOfficeId,
    sequenceNumber,
    unifiedNationalNumber,
    nationalId,
    (SELECT 
        paidStatus + ', ' + 
        bankName + ', ' + 
        Iban + ', ' + 
        WPSalary + ', ' + 
        paidYearMonth + ', ' + 
        deductionAmount + ', ' + 
        deduction + '; '
     FROM md AS md2
     WHERE md2.laborOfficeId = md.laborOfficeId 
       AND md2.sequenceNumber = md.sequenceNumber 
       AND md2.unifiedNationalNumber = md.unifiedNationalNumber 
       AND md2.nationalId = md.nationalId
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') AS paidStatusList
FROM md
GROUP BY laborOfficeId, sequenceNumber, unifiedNationalNumber, nationalId;


laborOfficeId
sequenceNumber
unifiedNationalNumber
nationalId
paidStatusList


1
123456
70023455436
1143575354
yes, alrajhi, SA4420000422608010555321, 5000.00, 202401, 73.00, Absents; yes, alrajhi, SA4420000422608010555321, 5000.00, 202402, 73.00, Absents; yes, alrajhi, SA4420000422608010555321, 5000.00, 202403, 73.00, Absents;


SELECT 
    laborOfficeId,
    sequenceNumber,
    unifiedNationalNumber,
    nationalId,
    (SELECT 
        paidStatus + CHAR(13) + CHAR(10) + 
        bankName + CHAR(13) + CHAR(10) + 
        Iban + CHAR(13) + CHAR(10) + 
        WPSalary + CHAR(13) + CHAR(10) + 
        paidYearMonth + CHAR(13) + CHAR(10) + 
        deductionAmount + CHAR(13) + CHAR(10) + 
        deduction   + CHAR(13) + CHAR(10)
     FROM md AS md2
     WHERE md2.laborOfficeId = md.laborOfficeId 
       AND md2.sequenceNumber = md.sequenceNumber 
       AND md2.unifiedNationalNumber = md.unifiedNationalNumber 
       AND md2.nationalId = md.nationalId
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') AS paidStatusList
FROM md
GROUP BY laborOfficeId, sequenceNumber, unifiedNationalNumber, nationalId;


laborOfficeId
sequenceNumber
unifiedNationalNumber
nationalId
paidStatusList


1
123456
70023455436
1143575354
yes
alrajhi
SA4420000422608010555321
5000.00
202401
73.00
Absents
yes
alrajhi
SA4420000422608010555321
5000.00
202402
73.00
Absents
yes
alrajhi
SA4420000422608010555321
5000.00
202403
73.00
Absents
laborOfficeId	sequenceNumber	unifiedNationalNumber	nationalId	paidStatus	bankName	Iban	WPSalary	paidYearMonth	deductionAmount	deduction
1	123456	70023455436	1143575354	yes	alrajhi	SA4420000422608010555321	5000.00	202401	73.00	Absents
1	123456	70023455436	1143575354	yes	alrajhi	SA4420000422608010555321	5000.00	202402	73.00	Absents
1	123456	70023455436	1143575354	yes	alrajhi	SA4420000422608010555321	5000.00	202403	73.00	Absents