Generate report converting rows into column using multiple tables

Question

I am working on a report with SQL Server database.

My tables are:

Table Name: 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

Table Name: 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

Table Name: 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

I need to generate following report:

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

I have tried PIVOT to convert the rows to column after creation of a view with all the data together. The problem I am facing there are:

My fee will be dynamic as is taxes
Putting Tax just after the fee.

Any help in this will be appreciated.

My Pivot Effort:

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;

Please show your pivot attempt. – Dale K Dec 21 '22 at 07:15 — Dale K, Dec 21 '22 at 07:15
@DaleK Added PIVOT effort in Body – Sanjay S Dec 21 '22 at 07:37 — Sanjay S, Dec 21 '22 at 07:37

score 2 · Accepted Answer · answered Dec 21 '22 at 07:57

An advice - if you add the table definitions and the data in your question, it will be easier for people to reproduce your case and help you. Like the following:

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

Then, if something is hard to be written as dynamic T-SQL, it's better to first write it as static like this:

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];

which is one variant of solving the issue and give us:

No, there are two dynamic parts of the above query, which can be calculated as follows:

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;

which give us:

Note, that I am ordering the fee_Type_name and tax_name by name. If you want to order them in a predefined way, you need to specify it in a separate tables (maybe).

seems useful for me, I will try the same – Sanjay S Dec 21 '22 at 09:33 — Sanjay S, Dec 21 '22 at 09:33

Generate report converting rows into column using multiple tables

1 Answers1