Migrating a Dynamic Pivot Query from SQL Server to PostgreSQL

More Info

In our efforts to assist clients transitioning their workloads from SQL Server to PostgreSQL, we frequently encounter instances where the PIVOT function is heavily utilized for creating dynamic reports. A previously documented method can be found in the Microsoft SQL Server 2019 to Amazon Aurora PostgreSQL Migration Playbook, which utilizes CASE WHEN statements for each pivoted column. However, this technique has certain drawbacks. As the number of pivoted columns grows, maintaining the code becomes cumbersome, and adding a new column necessitates modifications to the source code.

In this article, we will illustrate how to employ the crosstab function from PostgreSQL’s tablefunc extension to replicate the functionality of SQL Server’s PIVOT function, offering enhanced flexibility.

Overview of the Solution

To demonstrate this solution, we will use crosstab as the basis for implementing PIVOT-like functionality in PostgreSQL through the function get_dynamic_pivot_data. The significant advantage of crosstab is its ability to dynamically generate columns based on the results of the specified queries, making it adaptable for various datasets. You will learn how to handle multiple fixed and variable columns in a pivot table, alongside managing the corresponding cursor in the PostgreSQL function. This approach includes working with a PostgreSQL refcursor. The function can be executed using either psql or C#. For guidance, we will illustrate how to call it with a cursor parameter in psql. Typically, C# connects to PostgreSQL through Npgsql, and we will provide sample C# code demonstrating the use of the PostgreSQL function with Npgsql, including cursor variable management.

A diagram illustrating the solution architecture is included for reference.

Prerequisites

Ensure you have configured the necessary settings in your AWS account and your local installations, depending on how you plan to test the function.

Testing on the PostgreSQL Client Side

Follow these steps to test on the PostgreSQL client side:

1. Provision Amazon Aurora PostgreSQL-Compatible Edition or Amazon RDS for PostgreSQL.
2. Install a PostgreSQL client tool, such as pgAdmin on Amazon EC2 for Microsoft Windows Server, or psql on Amazon Linux 2023.

AWS CloudShell includes the PostgreSQL client tool (psql) version 15 by default. If you prefer not to provision an EC2 instance, you can use CloudShell to access Aurora PostgreSQL directly.

Testing with C#

For testing with C#, follow these steps. If you prefer not to use C#, you can skip these installations:

1. Download and install .NET SDK 8 on your EC2 instance. Use the following command to check the .NET version on Windows:

C:UsersAdministrator>dotnet --list-sdks
8.0.404 [C:Program Filesdotnetsdk]
C:UsersAdministrator>dotnet --version
8.0.404

2. Download and install Visual Studio Code on your EC2 instance for Windows.

Using the PIVOT Function on the SQL Server Side

To transition a pivot query with dynamic columns, begin by creating two tables and a stored procedure on the SQL Server side. You can find the relevant code in our Github repository. The following steps outline the process:

1. Create two tables with sample data. Here is the code to create the QuarterTbl:

CREATE TABLE QuarterTbl (
QuarterID INT NOT NULL IDENTITY PRIMARY KEY,
QuarterItem varchar(2)
);

INSERT INTO QuarterTbl([QuarterItem])
VALUES ('Q1'), ('Q2'), ('Q3'), ('Q4');

The resulting data from the QuarterTbl is as follows:

QuarterID     QuarterItem
------------ -----------
1             Q1
2             Q2
3             Q3
4             Q4

2. The code to create the ProductSales table is as follows:

CREATE TABLE ProductSales (
ProductID INT NOT NULL IDENTITY PRIMARY KEY,
ProductName varchar(10),
QuarterID int,
Year varchar(5),
Sales int,
FOREIGN KEY (QuarterID) REFERENCES QuarterTbl(QuarterID)
);

INSERT INTO ProductSales([ProductName],[QuarterID],[Year],[Sales])
VALUES
('ProductA', 1, 'Y2017', 100),
('ProductA', 2, 'Y2018', 150),
('ProductA', 2, 'Y2018', 200),
('ProductA', 1, 'Y2019', 300),
('ProductA', 2, 'Y2020', 500),
('ProductA', 3, 'Y2021', 450),
('ProductA', 1, 'Y2022', 675),
('ProductB', 2, 'Y2017', 0),
('ProductB', 1, 'Y2018', 900),
('ProductB', 3, 'Y2019', 1120),
('ProductB', 4, 'Y2020', 750),
('ProductB', 3, 'Y2021', 1500),
('ProductB', 2, 'Y2022', 1980);

The total sales figures for each quarter across the years can be displayed using the following query:

SELECT
PS.ProductName,
Q.QuarterItem,
PS.Year, SUM(PS.Sales) as QuarterSales
FROM ProductSales AS PS
INNER JOIN QuarterTbl AS Q
ON PS.QuarterID = Q.QuarterID
GROUP BY PS.ProductName, Q.QuarterItem, PS.Year
ORDER BY 1, 2, 3;

This displays quarterly sales data between 2017-2022:

ProductName   QuarterItem   Year      QuarterSales
-----------   -----------   -----     ------------
ProductA     Q1            Y2017    100
ProductA     Q1            Y2019    300
ProductA     Q1            Y2022    675
ProductA     Q2            Y2018    350
ProductA     Q2            Y2020    500
ProductA     Q3            Y2021    450
ProductB     Q1            Y2018    900
ProductB     Q2            Y2017    0
ProductB     Q2            Y2022    1980
ProductB     Q3            Y2019    1120
ProductB     Q3            Y2021    1500
ProductB     Q4            Y2020    750

3. Additionally, create a stored procedure called GetProductSalesReport using the PIVOT function and a dynamic query:

CREATE OR ALTER PROCEDURE GetProductSalesReport
@columns NVARCHAR(MAX)
AS
DECLARE @sql NVARCHAR(MAX);

SET @sql = 'SELECT * FROM
(
SELECT PS.ProductName, Q.QuarterItem, PS.Year, PS.Sales
FROM ProductSales PS
INNER JOIN QuarterTbl Q
ON PS.QuarterID = Q.QuarterID
) t
PIVOT (
SUM(Sales)
FOR [Year] IN ( ' + @columns + ')
) AS PV
ORDER BY 1, 2;';

EXECUTE sp_executesql @sql;
GO

4. Finally, execute the stored procedure with a parameter specifying the dynamic columns list to retrieve the pivoted results.

For more insights, check out this other blog post. If you want to delve deeper into this topic, chvnci.com offers excellent resources. Additionally, for a comprehensive view of the onboarding experience, visit Glassdoor.