Generate report converting rows into column using multiple tables
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;
Solution
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).
- Query to find position in a string before keyword and extract data before that position
- SQL: get next relative day of week. (Next Monday, Tuesday, Wed.....)
- #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’
- java.sql.SQLException: Column Index out of range, 2 > 1
- Should LIKE 'searchstr%' use an index?
- Multiple STRING_AGG in one query
- Postgres: Add constraint if it doesn't already exist
- UPDATE Statement ignores INNER JOIN
- Execute Stored Procedure Minimal API .Net
- Change default schema for user
- Oracle SQL - Select oldest of 3 dates, but ignore NULLs
- Housing Society management system database structure
- Keep getting error: PLS-00103: Encountered the symbol "CREATE"
- How to use an UPDATE Query with an INNER JOIN to update fields within a table
- Extract 4 million rows from database in chunks of 1 million rows each
- Aggregate into custom groups
- SQL query for chat application
- SQL basic problem when trying to SELECT 4 atributes and use GROUP BY with only 2
- How to modify query converting DATEDIFF to Oracle accepted syntax?
- Sales data analysis: calculate percentage of each product as per total sales
- Calculate the sum of values for the columns that meet the criteria
- Insert missing rows in time series consumption history
- Oracle database dependencies in PL/SQL
- How to autofill a field with data from another field in the same table?
- In Oracle: How to sort by group/cluster by a column value
- oracle SQL query - add date rows using different date
- How to fix products without cover image in PrestaShop
- IF ROW_COUNT() return error
- Select with like is not returning the correct results
- I am trying to get the start date ONLY from the column below using a SUBSTRING. Can someone tell me what I'm doing wrong?