AWS Global Infrastructure

Databases

Topics Covered
  • Cassandra (14 Blogs)
  • MongoDB Dev and Admin (15 Blogs)
  • MySQL (55 Blogs)
  • SQL Essentials Training and Certification (9 Blogs)
SEE MORE

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-widget-banner-bg

SQL Pivot – Know how to convert rows to columns

Published on Oct 18,2019 100 Views
37 / 37 Blog from Introduction to SQL

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-mobile-banner-bg

myMock Interview Service for Real Tech Jobs

  • Mock interview in latest tech domains i.e JAVA, AI, DEVOPS,etc
  • Get interviewed by leading tech experts
  • Real time assessment report and video recording

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?

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 runs 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.

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:

SupplierIDDaysofManufactureCostCustomerIDPurchaseID
112123011P1
221154322P2
332234511P3
414876522P1
542345233P3
631543133P1
741234211P2
854365422P2
933123411P3
1056683233P2

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:

CustomerID AverageCostofCustomer
111787.75
224654
335238.33

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:

Cost_According_To_Customers112233
AverageCostofCustomer1787.7546545238.33

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 did the PIVOT clause work.

Working of PIVOT clause

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

  • 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 KeywordUNPIVOT”.

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:

SupplierIDAAABBBCCC
1356
2928
3817

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
SupplierIDCustomersProducts

1

AAA

3

1

BBB

5

1

CCC

6

2

AAA

9

2

BBB

2

2

CCC

8

3

AAA

8

3

BBB

1

3

CCC

7

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.

Comments
0 Comments

Browse Categories

webinar REGISTER FOR FREE WEBINAR
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP

Subscribe to our Newsletter, and get personalized recommendations.