SQL Server动态行转列报表求助：税费字段联动排列问题

问题背景 #

基于SQL Server数据库开发报表，涉及三张核心表：appointments、payments、tax_details，表结构及数据如下：

表：appointments #

id       appointment_number   applicant_name            appointment_date   appointment_time
-----    ------------------   ------------------------  ----------------   ----------------
1        APP001               Anuj Tyagi                2022-12-28         11:00 AM
2        APP002               Puneet Pathak             2022-12-28         11:30 AM
3        APP003               Rajeev Kumar              2022-12-28         10:00 AM    

表：payments #

id       appointment_id       fee_Type_name    amount       
-----    ------------------   ---------------  ----------   
1        1                    Consulatncy      500
2        1                    Service Fee      100
3        1                    Pharmacy         435
4        2                    Consulatncy      800
5        2                    Service Fee      160
6        2                    Pharmacy         833
7        3                    Consulatncy      500
8        3                    Service Fee      100

表：tax_details #

id       payment_id       tax_name         tax_percentage    amount       
-----    --------------   ---------------  --------------    ---------
1        1                CGST             5.00              25.00 
2        1                SGST             2.50              12.50
3        2                CGST             8.00               8.00 
4        2                SGST             4.00               4.00
5        3                CGST             10.00             43.50 
6        3                SGST             8.00              34.80
7        4                CGST             5.00              40.00 
8        4                SGST             2.50              20.00
9        5                CGST             8.00              12.80 
10       5                SGST             4.00               6.40
11       6                CGST             10.00             83.30 
12       6                SGST             8.00              66.64
13       7                CGST             5.00              25.00 
14       7                SGST             2.50              12.50
15       8                CGST             8.00               8.00 
16       8                SGST             4.00               4.00

目标报表格式 #

appointment_number   applicant_name            appointment_date   appointment_time    Consulatncy      CGST    SGST   Service Fee     CGST    SGST    Pharmacy     CGST    SGST 
------------------   ------------------------  ----------------   ----------------    --------------   ------  ------ -------------   ------  ------  ----------   ------  ------   
APP001               Anuj Tyagi                2022-12-28         11:00 AM            500              25.00   12.50  100.00           8.00   4.00    435.00       43.50   34.50     
APP002               Puneet Pathak             2022-12-28         11:30 AM            800              40.00   20.00  160.00          12.80   6.40    833.00       83.30   66.64   
APP003               Rajeev Kumar              2022-12-28         10:00 AM            500              25.00   12.50  100.00           8.00   4.00      0.00        0.00    0.00 

遇到的问题 #

• 费用类型及税费均为动态，无法提前固定列名
• 无法将税费字段紧跟在对应费用字段之后

尝试的代码 #

SELECT    
    @@AllSumColumns = COALESCE(@AllSumColumns + ',','') + 'SUM(' + QUOTENAME([fee_name])+'))' + ' AS ' + QUOTENAME([fee_name])
FROM 
    (SELECT DISTINCT [fee_name] 
     FROM [dbo].vw_fee_list_details fld) AS PivotExample;

SELECT  @AllColumns = COALESCE(@AllColumns + ',','') + QUOTENAME([fee_name])
FROM 
    (SELECT DISTINCT [fee_type_name] 
     FROM [dbo].vw_fee_list_details fld 
     WHERE fee_type = 1 
       AND fld.appref_id LIKE CONCAT(@appRefId, '%') 
       AND fld.e_number LIKE CONCAT(@eNumber, '%')  
       AND fld.service_center LIKE CONCAT(@vscName, '%') 
       AND CAST(fld.transaction_date AS DATE) >= @startDate 
       AND CAST(fld.transaction_date AS DATE) <= @endDate
    ) AS PivotExample

SET   @SQLQuery = 
    N'SELECT
    ROW_NUMBER() over (Order by feeTable.appointment_number, feeTable.applicant_name, feeTable.transaction_date) [S.No],
    FORMAT(feeTable.transaction_date,''dd-MMM-yy'') as [Transaction Date],
    feeTable.transaction_time as [Transaction Time],
    feeTable.appointment_number as [Appointment Reference], 
    feeTable.applicant_name as [Applicant Name],
    +@AllSumColumns+'
FROM(
    SELECT * FROM(
        SELECT * FROM vw_fee_list_details fld 
        WHERE
            fld.appointment_number like '''+@appRefId+'%'+''' AND '+ 
            'fld.e_number like '''+@eNumber+'%'+'''
    ) a
    PIVOT (
    sum(amount)
    FOR [fee_name]
    IN('+@AllColumns+')) AS PivotTable) AS feeTable
    GROUP BY feeTable.appointment_number, feeTable.applicant_name, feeTable.transaction_date,feeTable.transaction_time'

EXEC sp_executesql @SQLQuery;

解决方案 #

要实现动态列且税费紧跟对应费用，需先整合费用与税费数据，再通过动态SQL按指定顺序构建列：

1. 核心思路 #

通过UNION ALL将费用和关联税费合并为同一数据集，用CASE语句实现动态聚合，确保每个费用后紧跟其CGST、SGST字段。

2. 完整实现代码 #

DECLARE @cols NVARCHAR(MAX);
DECLARE @sum_cols NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- 生成动态列定义：按「费用名称 → CGST → SGST」顺序
SELECT 
    @cols = COALESCE(@cols + ', ', '') + QUOTENAME(fee_Type_name) + ', ' + QUOTENAME(fee_Type_name + '_CGST') + ', ' + QUOTENAME(fee_Type_name + '_SGST'),
    @sum_cols = COALESCE(@sum_cols + ', ', '') + 
        'SUM(CASE WHEN fee_Type_name = ''' + fee_Type_name + ''' AND record_type = ''fee'' THEN value ELSE 0 END) AS ' + QUOTENAME(fee_Type_name) + ', ' +
        'SUM(CASE WHEN fee_Type_name = ''' + fee_Type_name + ''' AND record_type = ''CGST'' THEN value ELSE 0 END) AS ' + QUOTENAME(fee_Type_name + '_CGST') + ', ' +
        'SUM(CASE WHEN fee_Type_name = ''' + fee_Type_name + ''' AND record_type = ''SGST'' THEN value ELSE 0 END) AS ' + QUOTENAME(fee_Type_name + '_SGST')
FROM (SELECT DISTINCT fee_Type_name FROM payments) AS fees;

-- 构建最终动态SQL
SET @sql = N'
WITH combined_data AS (
    -- 费用数据
    SELECT 
        a.appointment_number,
        a.applicant_name,
        a.appointment_date,
        a.appointment_time,
        p.fee_Type_name,
        ''fee'' AS record_type,
        p.amount AS value
    FROM appointments a
    JOIN payments p ON a.id = p.appointment_id
    UNION ALL
    -- 税费数据
    SELECT 
        a.appointment_number,
        a.applicant_name,
        a.appointment_date,
        a.appointment_time,
        p.fee_Type_name,
        t.tax_name AS record_type,
        t.amount AS value
    FROM appointments a
    JOIN payments p ON a.id = p.appointment_id
    JOIN tax_details t ON p.id = t.payment_id
)
SELECT 
    appointment_number,
    applicant_name,
    appointment_date,
    appointment_time,
    ' + @sum_cols + '
FROM combined_data
GROUP BY appointment_number, applicant_name, appointment_date, appointment_time
ORDER BY appointment_date, appointment_time;
';

-- 执行动态SQL
EXEC sp_executesql @sql;

3. 适配筛选条件 #

如果需要添加原代码中的appRefId、eNumber等筛选条件，可在combined_data的两个SELECT分支中加入WHERE子句，示例：

-- 在费用数据分支添加筛选
SELECT 
    a.appointment_number,
    ...
FROM appointments a
JOIN payments p ON a.id = p.appointment_id
WHERE a.appointment_number LIKE ''' + @appRefId + '%''
  AND a.applicant_name LIKE ''' + @applicantName + '%''

4. 效果说明 #

• 自动适配payments表中新增的费用类型，无需修改代码
• 无对应税费的费用项会自动填充0.00
• 严格遵循「费用名称 → CGST → SGST」的列顺序

内容的提问来源于stack exchange，提问作者Sanjay S