Create Dynamic Pivot Table In MS SQL Server

 

--Create Table PivotTable

Create Table PivotTable(ProductName Varchar(100),MarketName Varchar(100),Price int)
Go

--insert Values Into Table
Insert Into PivotTable
Values
('Orange','Reliance Fresh',100),
('Mango','Big Bazar',120),
('Orange','Big Apple',30),
('Banana','CO-MART',150),
('Mango','CO-MART',75),
('Grape','Mandi',75)

Go

--Declaration Of Variable For Dynamic columns.

The Following Query is to Demonstrate Total Number Of columns That will Appear as column In Pivot Result.

Declare @PivotCols Varchar(2000)   
Set @PivotCols=''
Select @PivotCols=(Select Distinct '['+ MarketName +'],' from PivotTable for Xml Path(''))
Set @PivotCols=SUBSTRING(@PivotCols,1,len(@PivotCols)-1)
Select @PivotCols

Go

Dynamic columns Result Looks Like Following

 

Generate A Dynamic SQL To Get Dynamic Pivot

Declare @PivotCols Varchar(2000)   
Set @PivotCols=''
Select @PivotCols=(Select Distinct '['+ MarketName +'],' from PivotTable for Xml Path(''))
Set @PivotCols=SUBSTRING(@PivotCols,1,len(@PivotCols)-1)
--Dynemic Query To Run Pivot   
exec('Select * From PivotTable as tmp pivot (sum(price) for  MarketName in ('+@PivotCols+')) as tbl')

Go

Or

We Can Also Create Stored Procedure To Generate Results.

Create Procedure Usp_GetPivotTable
As
Declare @PivotCols Varchar(2000)   
Set @PivotCols=''
Select @PivotCols=(Select Distinct '['+ MarketName +'],' from PivotTable for Xml Path(''))
Set @PivotCols=SUBSTRING(@PivotCols,1,len(@PivotCols)-1)
--Dynemic Query To Run Pivot   
exec('Select * From PivotTable as tmp pivot (sum(price) for  MarketName in ('+@PivotCols+')) as tbl')
Go

 

Run The Query Or Execute the Store Procedure

Declare @PivotCols Varchar(2000)   
Set @PivotCols=''
Select @PivotCols=(Select Distinct '['+ MarketName +'],' from PivotTable for Xml Path(''))
Set @PivotCols=SUBSTRING(@PivotCols,1,len(@PivotCols)-1)
--Dynemic Query To Run Pivot   
exec('Select * From PivotTable as tmp pivot (sum(price) for  MarketName in ('+@PivotCols+')) as tbl')

--Or

Exec Usp_GetPivotTable

Go

 

 

Now We can Add More Rows To PivotTable And See the Result with Added Columns And Rows Automatically.