Database Simplified Headline Animator

Database Simplified

Wednesday, 1 June 2011

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

image

 

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

 

image

 

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

Surprised smile

1 comment:

  1. https://www.facebook.com/bantalsilikongrosir || http://bantalsilikon01.blogspot.com/ || http://jualsangkarpleci.blogspot.com/ || http://kopiluwakliar01.blogspot.com/ || http://vvty.in/uncategorized/marinir-seo/ || http://marinirseo.blogspot.com/ || http://marinir-seo.blogspot.com/ || http://bumbu-pecel-malang.blogspot.com/ ||http://distro-seo.blogspot.com/ || http://restoran-seo.blogspot.com/ || http://bantalmalangmurah.blogspot.com/ || http://distrobantal.blogspot.com/ || http://kesethandukmalang.blogspot.com/ ||

    http://tasya.marinirseo.web.id/ || http://caca.marinirseo.web.id/ ||http://anne.marinirseo.web.id/ ||http://jeannet.marinirseo.web.id/ ||http://jelita.marinirseo.web.id/ ||http://brenda.marinirseo.web.id/ ||http://ruth.marinirseo.web.id/ ||http://tasya1.marinirseo.web.id/ ||http://caca1.marinirseo.web.id/ ||http://anne1.marinirseo.web.id/ ||http://jeannet1.marinirseo.web.id/ ||http://jelita1.marinirseo.web.id/ ||http://brenda1.marinirseo.web.id/||http://ruth1.marinirseo.web.id/||http://tasya2.marinirseo.web.id/||http://caca2.marinirseo.web.id/ ||http://anne2.marinirseo.web.id/ ||http://jeannet2.marinirseo.web.id/ ||http://jelita2.marinirseo.web.id/ ||http://brenda2.marinirseo.web.id/||http://ruth2.marinirseo.web.id/||http://ruth3.marinirseo.web.id/||http://jelita3.marinirseo.web.id/||http://brenda3.marinirseo.web.id/||http://jeannet3.marinirseo.web.id/||http://anne3.marinirseo.web.id/||http://caca3.marinirseo.web.id/||http://tasya3.marinirseo.web.id/||http://tasya4.marinirseo.web.id/||http://caca4.marinirseo.web.id/||http://anne4.marinirseo.web.id/||http://jeannet4.marinirseo.web.id/||http://jelita4.marinirseo.web.id/||http://brenda4.marinirseo.web.id/||http://ruth4.marinirseo.web.id/||http://tasya5.marinirseo.web.id/||http://caca5.marinirseo.web.id/||http://anne5.marinirseo.web.id/||http://jelita5.marinirseo.web.id/||http://ruth5.marinirseo.web.id/ || http://marinirseo.web.id/ http://marinirseo.web.id/ http://marinirseo.web.id/ http://marinirseo.web.id/ http://marinirseo.web.id/ http://marinirseo.web.id/ http://marinirseo.web.id/
    http://tasya.marinirseo.web.id/ || http://caca.marinirseo.web.id/ ||http://anne.marinirseo.web.id/ ||http://jeannet.marinirseo.web.id/ ||http://jelita.marinirseo.web.id/ ||http://brenda.marinirseo.web.id/ ||http://ruth.marinirseo.web.id/ ||http://tasya1.marinirseo.web.id/ ||http://caca1.marinirseo.web.id/ ||http://anne1.marinirseo.web.id/ ||http://jeannet1.marinirseo.web.id/ ||http://jelita1.marinirseo.web.id/ ||http://brenda1.marinirseo.web.id/||http://ruth1.marinirseo.web.id/||http://tasya2.marinirseo.web.id/||http://caca2.marinirseo.web.id/ ||http://anne2.marinirseo.web.id/ ||http://jeannet2.marinirseo.web.id/ ||http://jelita2.marinirseo.web.id/ ||http://brenda2.marinirseo.web.id/||http://ruth2.marinirseo.web.id/||http://ruth3.marinirseo.web.id/||http://jelita3.marinirseo.web.id/||http://brenda3.marinirseo.web.id/||http://jeannet3.marinirseo.web.id/||http://anne3.marinirseo.web.id/||http://caca3.marinirseo.web.id/||http://tasya3.marinirseo.web.id/||http://tasya4.marinirseo.web.id/||http://caca4.marinirseo.web.id/||http://anne4.marinirseo.web.id/||http://jeannet4.marinirseo.web.id/||http://jelita4.marinirseo.web.id/||http://brenda4.marinirseo.web.id/||http://ruth4.marinirseo.web.id/||http://tasya5.marinirseo.web.id/||http://caca5.marinirseo.web.id/||http://anne5.marinirseo.web.id/||http://jelita5.marinirseo.web.id/||http://ruth5.marinirseo.web.id/ || http://marinirseo.web.id/ http://marinirseo.web.id/ http://marinirseo.web.id/ http://marinirseo.web.id/ http://marinirseo.web.id/ http://marinirseo.web.id/ http://marinirseo.web.id/

    http://bantal17.blogspot.com/http://sangkar07.blogspot.com/http://bumbu09.blogspot.com/http://sabun99.blogspot.com/http://keripik7.blogspot.com/http://kopiluwaknya.blogspot.com/

    ReplyDelete