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

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.
  1. CREATE TABLE StoreTbl
  2. (
  3. [StoreId] int,
  4. [WeekNumber] int,
  5. [Income] int
  6. );
Insert Data in Table
Now we insert some data in our table.
  1. INSERT INTO StoreTbl
  2. (
  3. [StoreId],
  4. [WeekNumber], [Income]
  5. )
  6. VALUES
  7. (102, 1, 96),
  8. (101, 1, 138),
  9. (105, 1, 37),
  10. (109, 1, 59),
  11. (101, 2, 282),
  12. (102, 2, 212),
  13. (105, 2, 78),
  14. (109, 2, 97),
  15. (105, 3, 60),
  16. (102, 3, 123),
  17. (101, 3, 220),
  18. (109, 3, 87);
We have the table data shown in the below image.
Create a Pivot Table
Using the below query, we can create a Pivot table.
  1. select StoreId,[1],[2],[3] from
  2. (
  3. select * from StoreTbl
  4. ) ST
  5. PIVOT
  6. (
  7. SUM(Income) for WeekNumber in ([1],[2],[3])
  8. )as StorePivot
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:
  1. Declare @ColumnName nvarchar(max)
  2. Declare @SQLQuery nvarchar(max)
  3. select @ColumnName=STUFF((select distinct ',['+ cast( WeekNumber as nvarchar)+']' from StoreTbl for xml path ('') ),1,1,'')
  4. print 'Column Names ....'
  5. print @ColumnName
  6. set @SQLQuery= 'select StoreId,'+@ColumnName+' from (
  7. select * from StoreTbl )
  8. ST
  9. PIVOT (
  10. SUM(Income) for WeekNumber in ('+@ColumnName+')
  11. )as StorePivot'
  12. print 'SQL Query ......'
  13. print @SQLQuery
  14. EXEC SP_EXECUTESQL @SQLQuery
Output
Explanation
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.