PIVOT in SQL

Introduction

In this article you will learn, how you can convert rows to columns in SQL server using PIVOT operator in SQL. Pivot table in SQL is same what you do in excel. It will covert rows to columns of SQL table.

What is Pivot in SQL 

Pivot in SQL table is a technique which is use to reverse the row items of on particular columns into multiple columns in SQL table. This method help to view aggregated row data into columns aggregated data. Pivoting in SQL is not straight as can be done in Microsoft excel, in SQL need to use PIVOT keyword and use SELECT command to make reverse rows into multiple columns.

Can we create pivot in SQL?

Yes, Pivot can be created in SQL table using PIVOT keyword in SQL query. with Select command you can create rows into columns and changed the table format into Pivot format.

Syntax of PIVOT keyword

Syntax in SQL is the use of PIVOT keyword in the SELECT command in SQL. You need to put PIVOT syntax after the FROM keyword as shown below.

SELECT 
        * [OR column_Name]
FROM         
       (SELECT                     
             [Col1],[Col2], ... ,        
        FROM                     
             Table_name       
        WHERE                     
             Condition ) t 
        PIVOT
             (     
              COUNT(Col)     
              FOR Col IN (Col1,       
                          Col2,        
                          Col3...,         
                          ) 
               ) AS pivot_table

lets explain the above syntax,

• First Select Command is used to display all Columns which Rows is converted into the columns
• Second SELECT command is used for columns which used for PIVOT
• Second FROM is use for the table which is using as PIVOT
• WHERE clause used for condition (it is optional to use)
• PIVOT operator used to convert roes in to Columns
• COUNT is used to aggregate the umbers of rows appear for each new columns
• FOR specified the columns which will used to convert their rows into columns
• and at the end used AS the pivot name, which you can use any name

Understand Pivot in SQL with example 

Let’s have example with one table in SQL. Here we would like to have Order priority in columns with count of occurrence for Customer ID = “5000”

Existing table have layout “Order Priority” in columns. if we would like to coverts all data in rows into the columns meaning to bring “Not Specified”, “Medium”, “High”, “Low” into columns, as you can se in second image

After applying PIVOT operator the desire output will be look like this as shown below image. 

Its converted the columns into the rows it is same like what you can also do in excel with pivot functionality only difference is that in excel you need to drag and drop in table and in SLQ you need to learn the syntax of PIVOT command in SQL.

Lets understand with the more examples as shown below

Example #1:  Simple PIVOT to convert Rows into Columns

Lets take one below query as example to covert Order_Priority rows into the columns

SELECT
     Customer_Name, Customer_Segment
FROM CUSTOMERS tbl
   PIVOT
       (    
          COUNT(Customer_ID)  
         FOR Customer_Segment IN ([Corporate],      
                                  [Home Office],       
                                  [Small Business],        
                                  [Consumer])
         ) AS pivot_table

Example #2:  PIVOT In SQL using nested SELECT command

Lets take one below query as example to covert Order_Priority rows into the columns

SELECT 
      *
FROM         
     (SELECT                     
     [Customer_ID],[Order_Priority]        
     FROM                     
         Orders_Tbl       
     WHERE                     
         [Customer_ID] = '5000' ) t 
     PIVOT
         (     
          COUNT([Order_Priority])     
          FOR [Order_Priority] IN ([Not Specified],       
                                   [Medium],        
                                   [Low],         
                                   [High]) 
          ) AS pivot_table

Explanation of above example/syntax: above example try to convert  Order_Priority rows data into the columns as headers.

First select command: The first select command display the all columns coming from second select command.

Second select command: Display the rows into the column for Order_Priority, by using PIVOT function,

Count keyword: This counts the number of rows for each Order_Priority categories

For / In : specify which columns data need to convert from rows to columns

Below image shows the result after Pivot

Result: