Convert Rows to Columns Using ‘Pivot' in SQL Server

In SQL Server, the rows will be converted into columns to make the data analysis and report generation easier. This conversion method is called “pivoting” the data. In SQL Server, the pivot operator is used to convert rows into columns, which makes the data more efficient. In this article, you will learn about Pivot in SQL Server, various methods used with Pivot, and best practices to follow.

Table of Contents:

• What is PIVOT in SQL Server?
• Using PIVOT with Aggregate Functions in SQL Server
  • Using the AVG() function with PIVOT in SQL Server
  • Using the COUNT() function with PIVOT in SQL Server
  • Using MIN() with PIVOT in SQL Server
  • Using the MAX() function with PIVOT in SQL Server
• Best Practices to Convert Rows to Columns using PIVOT in SQL Server
• Use Cases of Converting Rows to Columns using PIVOT in SQL Server
• Performance Consideration
• Alternative Method: Dynamic PIVOT in SQL Server
• Conclusion

What is PIVOT in SQL Server?

The PIVOT operator in SQL Server is used to modify the data. It converts unique values from one column to multiple columns in the result. When you want to summarize the data, like a sales sheet or reports, you can use the pivot operator to convert the logs into columns for easier analysis. The PIVOT operator uses many aggregate functions to get the desired results. 

Syntax for PIVOT operator in SQL Server

SELECT 
    NonPivotedColumn,
    [PivotedValue1] AS ColumnAlias1,
    [PivotedValue2] AS ColumnAlias2,
    ...
FROM 
    (SELECT NonPivotedColumn, PivotColumn, ValueColumn 
     FROM SourceTable) AS SourceTableAlias
PIVOT 
(
    AggregationFunction(ValueColumn)  -- SUM, AVG, COUNT, etc.
    FOR PivotColumn IN ([PivotedValue1], [PivotedValue2], ...)
) AS PivotTableAlias;

Example: To get sales reports of two companies using the SUM() function

– Creation of SalesData Table
CREATE TABLE SalesData (
    Region VARCHAR(50),
    Product VARCHAR(50),
    Sales INT
);

– Inserting the values into the SalesData Table
INSERT INTO SalesData (Region, Product, Sales) VALUES
('Apple', 'A', 100),
('Apple', 'B', 150),
('Apple', 'C', 200),
('Samsung', 'A', 250),
('Samsung', 'B', 300),
('Samsung', 'C', 350);

SELECT 
    Region,
    [A] AS Sales_A,
    [B] AS Sales_B,
    [C] AS Sales_C
FROM 
    (SELECT Region, Product, Sales FROM SalesData) AS SourceTable
PIVOT 
(
    SUM(Sales) FOR Product IN ([A], [B], [C])
) AS PivotTable;

Output:

Explanation: The PIVOT query converted the data from rows to columns, and the SUM() functions fetched the sales data together.

Using PIVOT with Aggregate Functions in SQL Server

1. Using the AVG() function with PIVOT in SQL Server

The AVG() function with the PIVOT operator in SQL Server will calculate the average value of a specified column for each pivoted column. This AVG function is useful when it is combined with PIVOT to analyze the average values instead of combining all the values. 

Example:

CREATE TABLE StudentScores (
    StudentName VARCHAR(50),
    SubjectName VARCHAR(50),
    Score INT
);
INSERT INTO StudentScores (StudentName, SubjectName, Score) VALUES
('Oviya', 'Datascience', 85),
('Oviya', 'Datascience', 90),
('Oviya', 'AI', 78),
('Oviya', 'AI', 82),
('Manu', 'Datascience', 88),
('Manu', 'AI', 75),
('Manu', 'Finance', 92),
('pandit', 'Datascience', 95),
('pandit', 'AI', 80),
('pandit', 'Finance', 85);
SELECT 
    StudentName,
    [Datascience] AS Avg_Datascience,
    [AI] AS Avg_AI,
    [Finance] AS Avg_Finance
FROM 
    (SELECT StudentName, SubjectName, Score FROM StudentScores) AS SourceTable
PIVOT 
(
    AVG(Score) FOR SubjectName IN ([Datascience], [AI], [Finance])
) AS PivotTable;

Output:

Explanation: The AVG() function with PIVOT calculated the average score of students.

2. Using the COUNT() function with PIVOT in SQL Server

 The COUNT() function with PIVOT in SQL Server is useful when you want to count the number of values present in the dataset. The COUNT() function is very helpful when we need to count the entry logs or transactions in a dataset.  

Example:

CREATE TABLE Transactions (
    TransactionID INT IDENTITY(1,1),
    CustomerName VARCHAR(50),
    Courses VARCHAR(50)
);
INSERT INTO Transactions (CustomerName, Courses) VALUES
('Kiran', 'Python'),
('Kiran', 'Python'),
('Kiran', 'AI'),
('Dhanush', 'Python'),
('Dhanush', 'Java'),
('Dhanush', 'Java'),
('Niru', 'AI'),
('Niru', 'AI'),
('Niru', 'Python');

SELECT 
    CustomerName,
    [Python] AS Python_Count,
    [AI] AS AI_Count,
    [Java] AS Java_Count
FROM 
    (SELECT CustomerName, Courses FROM Transactions) AS SourceTable
PIVOT 
(
COUNT(Courses) FOR Courses IN ([Python], [AI], [Java])
) AS PivotTable;

Output:

Explanation: The COUNT() function counted how many times the students bought the courses.

3. Using MIN() with PIVOT in SQL Server

The MIN() function with PIVOT in SQL Server is used when you need to find the minimum value in a column or dataset from different categories. This will be helpful when you need to find the lowest score on a report card or the lowest price of a product. 

Example:

CREATE TABLE StudentScores (
    StudentName VARCHAR(50),
    SubjectName VARCHAR(50),
    Score INT
);
INSERT INTO StudentScores (StudentName, SubjectName, Score) VALUES
('Apsara', 'SQL', 85),
('Apsara', 'SQL', 90),
('Apsara', 'Cybersecurity', 78),
('Apsara', 'Cybersecurity', 82),
('Nataraj', 'SQL', 88),
('Nataraj', 'Cybersecurity', 75),
('Nataraj', 'Finance', 92),
('Ramu', 'SQL', 95),
('Ramu', 'Cybersecurity', 80),
('Ramu', 'Finance', 85),
('Ramu', 'Finance', 78);
SELECT 
    StudentName,
    [SQL] AS Min_SQL_Score,
    [Cybersecurity] AS Min_Cybersecurity_Score,
    [Finance] AS Min_Finance_Score

FROM 
    (SELECT StudentName, SubjectName, Score FROM StudentScores) AS SourceTable
PIVOT 
(
    MIN(Score) FOR SubjectName IN ([SQL], [Cybersecurity], [Finance])
) AS PivotTable;

Output:

Explanation: The MIN() with the PIVOT operator in SQL Server fetched the details of students who got minimum scores from different columns.

4. Using the MAX() function with PIVOT in SQL Server

The MAX() function with PIVOT in SQL Server is used to find the maximum value in a column across different categories. The MAX() function will be helpful if you want to find the maximum marks or maximum salary of an employee. 

Example:

CREATE TABLE EmployeeSalaries (
    EmployeeName VARCHAR(50),
    Department VARCHAR(50),
    Salary INT
);
INSERT INTO EmployeeSalaries (EmployeeName, Department, Salary) VALUES
('Vel', 'HR', 50000),
('Vel', 'HR', 55000),
('Vel', 'IT', 70000),
('samyuktha', 'IT', 80000),
('samyuktha', 'Finance', 75000),
('samyuktha', 'Finance', 77000),
('rohith', 'HR', 52000),
('rohith', 'IT', 82000),
('rohith', 'Finance', 79000),
('rohith', 'Finance', 81000);
SELECT 
    EmployeeName,
    [HR] AS Max_HR_Salary,
    [IT] AS Max_IT_Salary,
    [Finance] AS Max_Finance_Salary
FROM 
    (SELECT EmployeeName, Department, Salary FROM EmployeeSalaries) AS SourceTable
PIVOT 
(
    MAX(Salary) FOR Department IN ([HR], [IT], [Finance])
) AS PivotTable;

Output:

Explanation: The MAX() function fetched the maximum salary of the employees, and the PIVOT operator converted it into columns for easier access.

Best Practices to Convert Rows to Columns using PIVOT in SQL Server

• Use Proper Indexing: Have to create indexes for pivoted and aggregated columns that will improve performance.  
• Filtering data before pivoting: The data has to be pivoted before filtering it with the WHERE clause, this will reduce the data size and enhance the performance. 
• Avoid manually specifying column names: This can be overcome by using Dynamic SQL, which updates the column names automatically when new elements are added.
• Optimizing large pivot data: Using CTEs and temporary tables, the large datasets can be handled efficiently. 
• An alternative method for better performance: Using dynamic SQL or CASE + GROUP BY can be used instead of PIVOT for better performance.

Use Cases of Converting Rows to Columns using PIVOT in SQL Server

Case 1: Converting daily attendance records into monthly records:

We can convert daily attendance records into monthly records by using the PIVOT operator with the COUNT aggregate function.

Example:

CREATE TABLE AttendanceRecords (
    EmployeeName VARCHAR(50),
    AttendanceDate DATE,
    Status VARCHAR(10)  -- Values: 'Present', 'Absent', 'Leave'
);

INSERT INTO AttendanceRecords (EmployeeName, AttendanceDate, Status) VALUES
('rohith', '2024-02-01', 'Present'),
('rohith', '2024-02-02', 'Absent'),
('rohith', '2024-02-03', 'Present'),
('Ram', '2024-02-01', 'Present'),
('Ram', '2024-02-02', 'Present'),
('Ram', '2024-02-03', 'Leave'),
('Kishan', '2024-02-01', 'Absent'),
('Kishan', '2024-02-02', 'Present'),
('Kishan', '2024-02-03', 'Present');

SELECT EmployeeName, 
       [Present] AS Present_Count, 
       [Absent] AS Absent_Count, 
       [Leave] AS Leave_Count
FROM (
    SELECT EmployeeName, Status FROM AttendanceRecords
) AS SourceTable
PIVOT (
    COUNT(Status) FOR Status IN ([Present], [Absent], [Leave])
) AS PivotTable;

Output:

Explanation: Here, COUNT() function fetches the login count of the employees, like present, absent, and leave for each employee.

Case 2: To track stock levels of products in different warehouses.

To track the stock levels of products in different warehouses, we can use PIVOT with the SUM() aggregate function in SQL Server. 

Example:

CREATE TABLE Inventory (
    Product VARCHAR(50),
    Warehouse VARCHAR(50),
    Stock INT
);
INSERT INTO Inventory (Product, Warehouse, Stock) VALUES
('Laptop', 'Apple', 30),
('Laptop', 'Samsung', 25),
('Laptop', 'Nokia', 40),
('Phone', 'Apple', 50),
('Phone', 'Samsung', 60),
('Phone', 'Nokia', 55),
('Tablet', 'Apple', 20),
('Tablet', 'Samsung', 15),
('Tablet', 'Nokia', 10);
SELECT Product, [Apple], [Samsung], [Nokia]
FROM (
    SELECT Product, Warehouse, Stock FROM Inventory
) AS SourceTable
PIVOT (
    SUM(Stock) FOR Warehouse IN ([Apple], [Samsung], [Nokia])
) AS PivotTable;

Output:

Explanation: Here, the SUM() function gathered all the records of stocks that were present in different warehouses, and the PIVOT operator combined them in a column.

Performance Consideration

1. Indexing for speed: To optimize query performance, it is crucial to ensure appropriate indexes exist for all columns used for PIVOT for grouping and pivoting in a query.
2. Filtering before pivoting: It is best to apply WHERE filters in the subquery to reduce data volume in advance before pivoting. This arrangement has the advantages of lesser memory usage and faster execution on account of memory and aggregation optimization.
3. Static vs Dynamic columns: PIVOT requires explicit column names, which means that it becomes rigid when the dataset changes. If the column values are frequently changing, one might want to consider the dynamic SQL approach.
4. Memory Usage: While performing large PIVOT operations, it may consume more memory, especially when it has many columns. 

Alternative Method: Dynamic PIVOT in SQL Server

The dynamic PIVOT in SQL Server is used to get the count of data that are being updated regularly, like transactions, server logs, social media postings, etc. The dynamic PIVOT will automatically update every record once it gets into the database. This will reduce the time of manually updating records. Dynamic PIVOT can handle multiple columns at the same time. 

Example:

CREATE TABLE InfluencerPosts (
    Influencer VARCHAR(50),
    Platform VARCHAR(50),
    PostCount INT
);

INSERT INTO InfluencerPosts (Influencer, Platform, PostCount) VALUES
('Kavin', 'Instagram', 120),
('Kavin', 'X', 85),
('Kavin', 'YouTube', 50),
('Priya', 'Instagram', 100),
('Priya', 'YouTube', 70),
('Priya', 'Intellipaat website', 90),
('Pritham', 'X', 110),
('Pritham', 'Intellipaat website', 80),
('Pritham', 'Instagram', 95);
DECLARE @columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- Step 1: Get the distinct platform names dynamically
SELECT @columns = STRING_AGG(QUOTENAME(Platform), ',') 
FROM (SELECT DISTINCT Platform FROM InfluencerPosts) AS PlatformList;

-- Step 2: Construct the dynamic SQL PIVOT query
SET @sql = 
'SELECT Influencer, ' + @columns + '
FROM 
(
    SELECT Influencer, Platform, PostCount FROM InfluencerPosts
) AS SourceTable
PIVOT 
(
    SUM(PostCount) FOR Platform IN (' + @columns + ')
) AS PivotTable;';

-- Step 3: Execute the dynamic SQL query
EXEC sp_executesql @sql;

Output:

Explanation: The dynamic PIVOT in SQL Server combined the count of all the posts that influencers posted on social media platforms that update every time someone uploads a new post. 

Conclusion

The PIVOT operator in SQL Server is a powerful data manipulation tool that converts data from a row format to a column format, which makes analysis and report generation easier. It works with several aggregate functions, such as SUM(), AVG(), COUNT(), MIN(), and MAX(), that summarize the data efficiently. PIVOT can use, among many others, the analysis of data such as sales, student scores, attendance, or stock levels in different categories. Performance such as indexing, filtering, and memory must be considered for a faster query. To learn more about SQL, check out the SQL Certification.

FAQs