Learn About Pivot In SQL Server

 

Introduction 

 

Hello Guys,

 

In this article, I will tell you about Pivot in SQL. In this article we will discuss what is Pivot, the syntax of Pivot, how to implement Pivot, how to Implement dynamic columns in Pivot, and much more. I hope this article helps you.

 

What Is a Pivot Clause? 

Pivot is a relational operation available in SQL server that allows users to convert row-level data to the column level. A Pivot clause allows you to write cross-tabulation. This means that you can aggregate your results and rotate rows into columns.

 

Syntax 

1. SELECT Column_Name AS < Column_Name_alias>,  
2. [pivot_value1], [pivot_value2], ... [pivot_value_n]  
3. FROM  
4. (  
5.           <TableName>) AS < TableName_alias>  
6. PIVOT  
7. (  
8.          aggregate_function(<aggregate_column>)  
9.          FOR <pivot_column>  
10.          IN ([pivot_value1], [pivot_value2], ... [pivot_value_n])  
11. ) AS <pivot_table_alias>; 

Syntax Explanation

• Column_Name
  This column is the table column that shows up as the first column in a Pivot table. It's mostly a column that has unique data used as the first column in a Pivot table.
  
  
• Column_Name_alias
  This is alias of that column. In simple words, the value of this is shown as a label in header of this column.
  
  
• Pivot Values
  This is your column values, not the column name but a unique value inside your column. This value can be any number.
  
  
• TableName and TableName_alias
  This is your table name on which you want to perform the Pivot operation and the alias is a label that works as a table name.
  
  
• Pivot
  Using this clause we can create a pivot table.
  
  
• Aggregate Function and Aggregate Column
  If you perform a Pivot clause, you need to use aggregate functions like MIN(), MAX(), SUM(), AVG(), COUNT(), etc. if you do not use the aggregate function, then it will generate a syntax error and you can only use one column in this section.
  
  
• pivot_column
  This is the column that contains Pivot values.

Create Table to Perform Operation on It

 

Here we create a table called StoreTbl which has three columns, StoreId : The ID of Store, WeekNumber : Week number of collection, and Income : Total income of that week. 

Insert Data in Table 

Now we insert some data in our table.

We have the table data shown in the below image.

 

Create a Pivot Table 

Using the below query, we can create a Pivot table.

Output

Use Dynamic Pivot Column 

In many scenarios, we don’t how which value can be used as a pivot column because we have lots of data, and creating columns one by one is difficult. In this case, we define dynamic Pivot columns to create a Pivot table. Let’s take the below example:

Output

Explanation 

• In the above query, we declare two variables, ColumnName and SQLQuery.
• Then we assign the distinct value of the Week column to the ColumnName variable with comma separation and enclose it in a big bracket. Using this, we get data like [1],[2],[3]. We can use this data as a Pivot column.
• Then we create a query in single quotes because we assign Pivot columns in variable and if we execute the direct query with a variable, then we get a syntax error.
• Like syntax, we create queries to generate a Pivot table.
• In the end, we execute an SQLQuery variable and get the same output as we get above.

After executing the above query, we get the following print lines in the message tab. 

I hope you find this article useful and get some help. If you like this article, please share it with your friends. Thank you.