sql - 使用多个表生成将行转换为列的报告

Question

我正在使用 SQL Server 数据库编写报告。

我的表是:

表名:约会

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 

在创建包含所有数据的 View 后，我尝试使用 PIVOT 将行转换为列。我面临的问题是:

1. 我的费用和税收一样是动态的
2. 在费用之后征税。

我们将不胜感激。

我的枢轴努力:

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;

Answer 1

一个建议 - 如果您在问题中添加表格定义和数据，人们将更容易重现您的案例并为您提供帮助。像下面这样:

DROP TABLE IF EXISTS #appointments;
DROP TABLE IF EXISTS #payments;
DROP TABLE IF EXISTS #tax_details;

CREATE TABLE #appointments
(
    [id] INT PRIMARY KEY
   ,[appointment_number] VARCHAR(12)
   ,[applicant_name] VARCHAR(32)
   ,[appointment_date] DATE
   ,[appointment_time] TIME
);

CREATE TABLE #payments
(
    [id] INT PRIMARY KEY
   ,[appointment_id] INT
   ,[fee_Type_name] VARCHAR(32)
   ,[amount] MONEY
);

CREATE TABLE #tax_details
(
    [id] INT PRIMARY KEY
   ,[payment_id] INT
   ,[tax_name] VARCHAR(8)
   ,[tax_percentage] DECIMAL(9,2)
   ,[amount] MONEY
);

INSERT INTO #appointments ([id], [appointment_number], [applicant_name], [appointment_date], [appointment_time])
VALUES (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');

INSERT INTO #payments ([id], [appointment_id], [fee_Type_name], [amount])
VALUES (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);

INSERT INTO #tax_details ([id], [payment_id], [tax_name], [tax_percentage], [amount])
VALUES (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);

那么，如果有些东西很难写成动态的T-SQL，最好先写成静态的，像这样:

SELECT PVT.[id]  
      ,PVT.[appointment_number]
      ,PVT.[applicant_name]
      ,PVT.[appointment_date]
      ,PVT.[appointment_time]
      ,ISNULL(MAX(PVT.[Consulatncy]), 0) AS [Consulatncy]
      ,ISNULL(MAX(IIF(PVT.[Consulatncy] IS NOT NULL AND T.[tax_name] = 'CGST', T.[amount], NULL)), 0) AS [CGST]
      ,ISNULL(MAX(IIF(PVT.[Consulatncy] IS NOT NULL AND T.[tax_name] = 'SGST', T.[amount], NULL)), 0) AS [SGST]
      ,ISNULL(MAX(PVT.[Service Fee]), 0) AS [Service Fee]
      ,ISNULL(MAX(IIF(PVT.[Service Fee] IS NOT NULL AND T.[tax_name] = 'CGST', T.[amount], NULL)), 0) AS [CGST]
      ,ISNULL(MAX(IIF(PVT.[Service Fee] IS NOT NULL AND T.[tax_name] = 'SGST', T.[amount], NULL)), 0) AS [SGST]
      ,ISNULL(MAX(PVT.[Pharmacy]), 0) AS [Pharmacy]
      ,ISNULL(MAX(IIF(PVT.[Pharmacy] IS NOT NULL AND T.[tax_name] = 'CGST', T.[amount], NULL)), 0) AS [CGST]
      ,ISNULL(MAX(IIF(PVT.[Pharmacy] IS NOT NULL AND T.[tax_name] = 'SGST', T.[amount], NULL)), 0) AS [SGST]
FROM
(
    SELECT A.[id]   
          ,A.[appointment_number]
          ,A.[applicant_name]
          ,A.[appointment_date]
          ,A.[appointment_time]
          ,P.[id] AS [payment_id]
          ,P.[fee_Type_name]
          ,P.[amount]
    FROM #appointments A
    INNER JOIN #payments P
        ON A.[id] = P.[appointment_id]
) DS
PIVOT
(
    MAX([amount]) FOR [fee_Type_name] IN ([Consulatncy], [Service Fee], [Pharmacy])
) PVT
INNER JOIN #tax_details T
    ON PVT.[payment_id] = T.[payment_id]
GROUP BY PVT.[id]    
        ,PVT.[appointment_number]
        ,PVT.[applicant_name]
        ,PVT.[appointment_date]
        ,PVT.[appointment_time];

这是解决问题的一种变体，给我们:

不是，上面的query有两个动态部分，可以这样计算:

DECLARE @DynamicTSQLStatement NVARCHAR(MAX)
       ,@DynamicPVTColumns VARCHAR(MAX)
       ,@DynamicSELECTColumns VARCHAR(MAX);

SELECT @DynamicPVTColumns = STRING_AGG(QUOTENAME([fee_Type_name]), ', ')
FROM
(
    SELECT DISTINCT [fee_Type_name]
    FROM #payments
) DS;

SELECT @DynamicSELECTColumns = STRING_AGG(CAST(IIF([tax_name] IS NOT NULL, 'ISNULL(MAX(PVT.[' + [fee_Type_name] + ']), 0) AS [' + [fee_Type_name] + ']', 'ISNULL(MAX(IIF(PVT.[' + [fee_Type_name] + '] IS NOT NULL AND T.[tax_name] = ''' + [tax_name] + ''', T.[amount], NULL)), 0) AS [' + [tax_name] + ']') AS VARCHAR(MAX)), ',') WITHIN GROUP (ORDER BY [ColumnID])
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY [fee_Type_name], [tax_name]) AS [ColumnID]
          ,[fee_Type_name]
          ,[tax_name]
    FROM
    (
        SELECT DISTINCT P.[fee_Type_name]
                       ,T.[tax_name]                
        FROM #payments P
        INNER JOIN #tax_details T
            ON p.[id] = T.[payment_id]
        UNION ALL
        SELECT DISTINCT [fee_Type_name]
                       ,NULL
        FROM #payments
    ) DS ([fee_Type_name], [tax_name])
) DS


SET @DynamicTSQLStatement = N'
SELECT PVT.[id]  
      ,PVT.[appointment_number]
      ,PVT.[applicant_name]
      ,PVT.[appointment_date]
      ,PVT.[appointment_time]
      ,' + @DynamicSELECTColumns + '
FROM
(
    SELECT A.[id]   
          ,A.[appointment_number]
          ,A.[applicant_name]
          ,A.[appointment_date]
          ,A.[appointment_time]
          ,P.[id] AS [payment_id]
          ,P.[fee_Type_name]
          ,P.[amount]
    FROM #appointments A
    INNER JOIN #payments P
        ON A.[id] = P.[appointment_id]
) DS
PIVOT
(
    MAX([amount]) FOR [fee_Type_name] IN (' + @DynamicPVTColumns + ')
) PVT
INNER JOIN #tax_details T
    ON PVT.[payment_id] = T.[payment_id]
GROUP BY PVT.[id]    
        ,PVT.[appointment_number]
        ,PVT.[applicant_name]
        ,PVT.[appointment_date]
        ,PVT.[appointment_time];

';


EXEC sp_executesql @DynamicTSQLStatement;

这给了我们:

请注意，我按名称订购了 fee_Type_name 和 tax_name。如果您想以预定义的方式对它们进行排序，则需要在单独的表中指定(可能)。