How to Use SQL Server PIVOT with Multiple Fixed Columns ?

In a previous technical article, we learned the PIVOT query with one unique fixed column, this time we’ll use a more complex use case, a SQL Server PIVOT case with multiple fixed columns. Indeed, in this different example, the table to pivot has 2 fixed non numeric columns. So the two columns will not be aggregated or calculated and they are also not pivoted.

1. Create a SQL table with two or more fixed columns to Pivot

Here we use the same process as in the previous article on the PIVOT operator. We simply group the data in a different way. Open SSMS and execute this query to cerate the table and insert some sample data. Indeed, we simply added the customer ID and Name as 2 new columns.

-- Create the sales table with 2 additional columns
-- The customer ID and the customer name
CREATE table [dbo].[SALES_with_Customers] (
    [MONTH]         NVARCHAR(20),
    [CUSTOMER_ID]   NVARCHAR(20),
    [CUSTOMER_NAME] NVARCHAR(20),
    [AMOUNT]        NUMERIC(5)
)
GO

-- Insert first sales amount for each month for Customer-001
INSERT dbo.SALES_with_Customers VALUES ( N'January',  N'Customer-001', N'ONE', 1000)
INSERT dbo.SALES_with_Customers VALUES ( N'February', N'Customer-001', N'ONE', 2000)
INSERT dbo.SALES_with_Customers VALUES ( N'March',    N'Customer-001', N'ONE', 3000)
INSERT dbo.SALES_with_Customers VALUES ( N'April',    N'Customer-001', N'ONE', 4000)
INSERT dbo.SALES_with_Customers VALUES ( N'May',      N'Customer-001', N'ONE', 5000)
INSERT dbo.SALES_with_Customers VALUES ( N'June',     N'Customer-001', N'ONE', 6000)
-- second step for the first customer
INSERT dbo.SALES_with_Customers VALUES ( N'January',  N'Customer-001', N'ONE', 1500)
INSERT dbo.SALES_with_Customers VALUES ( N'February', N'Customer-001', N'ONE', 1500)
INSERT dbo.SALES_with_Customers VALUES ( N'March',    N'Customer-001', N'ONE', 1500)
INSERT dbo.SALES_with_Customers VALUES ( N'April',    N'Customer-001', N'ONE', 1500)
INSERT dbo.SALES_with_Customers VALUES ( N'May',      N'Customer-001', N'ONE', 1500)
INSERT dbo.SALES_with_Customers VALUES ( N'June',     N'Customer-001', N'ONE', 1500)

-- Insert first sales amounts for each month for the Customer-002
INSERT dbo.SALES_with_Customers VALUES ( N'January',  N'Customer-002', N'TWO', 1100)
INSERT dbo.SALES_with_Customers VALUES ( N'February', N'Customer-002', N'TWO', 2200)
INSERT dbo.SALES_with_Customers VALUES ( N'March',    N'Customer-002', N'TWO', 3300)
INSERT dbo.SALES_with_Customers VALUES ( N'April',    N'Customer-002', N'TWO', 4400)
INSERT dbo.SALES_with_Customers VALUES ( N'May',      N'Customer-002', N'TWO', 5500)
INSERT dbo.SALES_with_Customers VALUES ( N'June',     N'Customer-002', N'TWO', 6600)
-- Insert second sales amount for each month for Customer-002
INSERT dbo.SALES_with_Customers VALUES ( N'January',  N'Customer-002', N'TWO', 2000)
INSERT dbo.SALES_with_Customers VALUES ( N'February', N'Customer-002', N'TWO', 2000)
INSERT dbo.SALES_with_Customers VALUES ( N'March',    N'Customer-002', N'TWO', 2000)
INSERT dbo.SALES_with_Customers VALUES ( N'April',    N'Customer-002', N'TWO', 2000)
INSERT dbo.SALES_with_Customers VALUES ( N'May',      N'Customer-002', N'TWO', 2000)
INSERT dbo.SALES_with_Customers VALUES ( N'June',     N'Customer-002', N'TWO', 2000)

-- Check inserted data
SELECT 	*
FROM 	dbo.[SALES_with_Customers];

Note that the source table contains 12 lines per Customer ID and Customer Name, for the 6 first months of the year.

*Use a query in SSMS to create the table with multiple fixed columns to pivot*

2. Use the PIVOT operator to manage multiple fixed fields

Once the new table is created, build the pivot query. The exact method is used here. The only difference with the more simple PIVOT example is that all the named columns are listed in the first select statement.

SELECT 	*
FROM (
    SELECT
	  [MONTH],
	  [CUSTOMER_ID],
	  [CUSTOMER_NAME],
	  [AMOUNT]
    FROM dbo.SALES_with_Customers
) AS Source_Table

PIVOT (
    AVG(AMOUNT)
    FOR MONTH IN ([January], [February], [March], [April], [May], [June])
) AS Pivot_Table;

Build the query using the same three steps.

From the source table, select all the columns to pivot and to display: Month, Customer_Id, Customer_Name and Amount
Configure the pivot column and the aggregation, here it’s AVG(AMOUNT)
Use the PIVOT operator to execute the transformation on the selected data.

*SQL Server PIVOT query example explained*

Finally execute the query and check the calculated average of the sales column, displayed in the Amount column.

The first customer’s data is available in one unique line now.
Same for Customer-002.
Each month is now displayed in a specific dedicated column.

*Table before and after the PIVOT of the rows into columns with SQL Server*

The ultimate goal of aggregation can be very different in the usage, it can be to use data for Reporting using one of the tools on the market. But it can also be to limit the number of lines stored and have a better management of databases volumetry.

About the T-SQL PIVOT operator with fixed data

In conclusion, this exploration of SQL Server PIVOT with multiple fixed columns significantly enhances our ability to manage and interpret complex datasets. By maintaining certain columns fixed while pivoting others, we unlock new dimensions of data analysis and reporting, showcasing the versatility and power of SQL Server for sophisticated data manipulation tasks.