SQL Pivot – Know how to convert rows to columns

Relational databases store humongous amounts of data in the form of tables. These tables can have any number of rows and columns. But, what if you had to change the row-level data to columnar data? Well, in this article on SQL Pivot, I will show you how you can convert rows to a column in a SQL Server.

The following topics will be covered in this article:

• What is PIVOT in SQL?
• Syntax
• Example
• Working of PIVOT Clause
• SQL UNPIVOT

What is PIVOT in SQL?

PIVOT is used to rotate the table-value by converting the unique values of a single column to multiple columns. It is used to rotate the rows to column values and run aggregations when required on the remaining column values.

UNPIVOT, on the other hand, is used to carry out the opposite operations. So, it is used to convert the columns of a specific table to column values.

Moving on in this article, let us understand the syntax of SQL Pivot. You can even check out the details of relational databases, functions, queries, variables, etc with the SQL Server Certification.

Syntax:

SELECT NonPivoted ColumnName,  
    [First Pivoted ColumnName] AS ColumnName,  
    [Second Pivoted ColumnName] AS ColumnName, 
    [Third Pivoted ColumnName] AS ColumnName,	
    ...  
    [Last Pivoted ColumnName] AS ColumnName  
FROM  
    (SELECT query which produces the data)   
    AS [alias for the initial query]  
PIVOT  
(  
    [AggregationFunction](ColumName)  
FOR   
[ColumnName of the column whose values will become column headers]   
    IN ( [First Pivoted ColumnName], [Second Pivoted ColumnName],  [Third Pivoted ColumnName]
    ... [last pivoted column])  
) AS [alias for the Pivot Table];

Here, you can also use the ORDER BY clause to sort the values either in the ascending or descending order. Now that you know what is PIVOT in SQL and its basic syntax, let us move forward and see how to use it.

Examples

For your better understanding, I will be considering the following table to explain you all the examples.

Supplier Table:

Let us write a simple query to retrieve the average cost spent by each customer.

SELECT CustomerID, AVG(Cost) AS AverageCostofCustomer   
FROM Suppliers  
GROUP BY CustomerID;  

Output:

Now, let us say we want to pivot the above table. Here, the CustomerID column values will become the column headers.

-- Create Pivot table with one row and three columns
SELECT 'AverageCostofCustomer' AS Cost_According_To_Customers,
[11], [22], [33]
FROM
(SELECT CustomerID, Cost
FROM Suppliers) AS SourceTable
PIVOT
(
AVG(Cost)
FOR CustomerID IN ([11], [22], [33])
) AS PivotTable;

Output:

Note: When you use aggregate functions with PIVOT, null values are not considered while computing an aggregation.

Well, that was a basic example, but let us now understand how the PIVOT clause worked.

Working on PIVOT clause

You can refer above, to create a PIVOT TABLE, you need to follow the steps below:

• Select columns for pivoting
• Then, select a source table.
• Apply the PIVOT operator, and then use the aggregate functions.
• Mention pivot values.

Select columns for pivoting

Initially, we have to specify the fields to be included in our results. In our example, I considered the AverageCostofCustomer column in the Pivot table. Then we created three other columns with the column headers 11, 22, and 33. Example-

SELECT 'AverageCostofCustomer' AS Cost_According_To_Customers, [11], [22], [33]

Select source table

Next, you have to specify the SELECT statement which will return the source data for the pivot table. In our example, we are returning the CustomerID and Cost from the Suppliers table.

(SELECT CustomerID, Cost FROM Suppliers) AS SourceTable

Apply the PIVOT operator, and then use the aggregate functions

Next, you have to specify the aggregate function to be used while creating the pivot table. In our example, I have used the AVG function to calculate the average cost.

PIVOT ( AVG(Cost)

Mention pivot values

Finally, you have to mention the values which have to be included in the resultant pivot table. These values will be used as the column headings in the pivot table.

FOR CustomerID IN ([11], [22], [33]) ) AS PivotTable;

That’s how the PIVOT operators work. Moving on in this article on SQL PIVOT, let us understand how different it is from SQL UNPIVOT.

SQL UNPIVOT

The SQL UNPIVOT operator is used to carry out the opposite operation to that of PIVOT. It is used to rotate the column data into row-level data. The syntax, of UNPIVOT, is similar to that of PIVOT. The only difference is that you have to use the SQL Keyword “UNPIVOT”.

Example:

Let us create a table with the columns SupplierID, AAA, BBB, and CCC. Also, insert few values.

CREATE TABLE sampletable (SupplierID int, AAA int, BBB int, CCC int);  
GO  
INSERT INTO sampletable VALUES (1,3,5,6);  
INSERT INTO sampletable VALUES (2,9,2,8);  
INSERT INTO sampletable VALUES (3,8,1,7);  
GO  

Output:

Now, let us say, we want to unpivot the table. To to do that, you can refer to the following code:

SELECT SupplierID, Customers, Products
FROM
(SELECT SupplierD, AAA, BBB, CCC
FROM sampletable) p
UNPIVOT
(Products FOR Customers IN
(AAA, BBB, CCC)
)AS example;
GO

That’s how you can use the SQL PIVOT and UNPIVOT. With this, we come to an end to this article. I hope you understood, how to use SQL. If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of this article on SQL Pivot and I will get back to you.