Return custom query with columns and a list that contains other columns

I have this table structure

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');


SELECT * FROM md

I want to write a SQL query to return a different result

to return

laborOfficeId,
sequenceNumber,
unifiedNationalNumber,
nationalId

with a list of paidstatuslist which are an array of an objects contains other table column (paidStatus, bankName, Iban, WPSalary, paidYearMonth, deductionAmount, deduction)

If I query the main table, these columns are repeated with the same value where the list are different data

I searched for a solution found that this could happen using data tables insert and retrieve from it

Solution

You can use FOR XML PATH('') to concatenate the column values for each row into a single string. Just to clarify the .value('.', 'NVARCHAR(MAX)') will make sure that the result is treated as plain text if you are avoiding XML formatting

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

fiddle