Sql 返回包含列和包含其他列的列表的自定义查询

您可以使用FOR XML PATH(‘’)将每行的列值连接成单个字符串.只需澄清.Value(‘.，’NVARCHAR(Max)‘)将确保在避免使用XML格式时将结果视为纯文本

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

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;

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;

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;

fiddle个