In the Previous Post PIVOT and UNPIVOT in Sql Server explained how PIVOT relational operator can be used to transform columns distinct values as Columns in the result set by mentioning all the distinct column values in the PIVOT operators PIVOT columns IN clause. This type of PIVOT query is called Static PIVOT query, because if the PIVOT column in the source table get’s extra unique values after the initial query then that will not reflect in the PIVOT query result unless it is mentioned in the PIVOT Columns IN clause. Static PIVOT queries are fine as long as we know that the PIVOT column values never change, for instance if PIVOT column values are MONTH or Day of the Week or hour of the day etc.
In this Article will present how we can write a Dynamic PIVOT query with an example, where we don’t need to mention the PIVOT columns each unique values and no need to worry if PIVOT column gets extra unique values after the initial query.
ALSO READ: PIVOT and UNPIVOT in Sql Server
First Create a Temporary Table #CourseSales with sample records as depicted in the below image by using the following script:
--Create Temporary Table #CourseSales
CREATE TABLE #CourseSales
(Course VARCHAR(50),Year INT,Earning MONEY)
GO
--Populate Sample records
INSERT INTO #CourseSales VALUES('.NET',2012,10000)
INSERT INTO #CourseSales VALUES('Java',2012,20000)
INSERT INTO #CourseSales VALUES('.NET',2012,5000)
INSERT INTO #CourseSales VALUES('.NET',2013,48000)
INSERT INTO #CourseSales VALUES('Java',2013,30000)
GO
PIVOT #CourseSales Table data on the Course column Values
Let us first understand the Static PIVOT query and then see how we can modify this Static PIVOT query to Dynamic.
Static PIVOT query
Below Static PIVOT script pivots the #CourseSales Table data so that the Course columns distinct values are transformed as Columns in the result set as depicted in the above image.
SELECT *
FROM #CourseSales
PIVOT(SUM(Earning)
FOR Course IN ([.NET], Java)) AS PVTTable
RESULT:
Let us insert one more row in the #CourseSales table for the new course SQL Server with below insert statement.
INSERT INTO #CourseSales VALUES('Sql Server',2013,15000)
Now rerun the above PIVOT query.
RESULT:
From the above result it is clear that the newly added course Sql Server sales data is not reflected in the result.
Dynamic PIVOT Query
To make the above Static PIVOT query to dynamic, basically we have to remove the hardcoded PIVOT column names specified in the PIVOT operators PIVOT columns IN clause. Below query demonstrates this.
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
+ QUOTENAME(Course)
FROM (SELECT DISTINCT Course FROM #CourseSales) AS Courses
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
N'SELECT Year, ' + @ColumnName + '
FROM #CourseSales
PIVOT(SUM(Earning)
FOR Course IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery
[ALSO READ] How to replace NULL value by 0 in the Dynamic Pivot result – Sql Server
RESULT:
From the above result it is clear that this query is a True Dynamic PIVOT query as it reflected all the courses in the #CourseSales table without needing to write hardcoded course names in the PIVOT query.
Examples of PIVOT and Dynamic PIVOT
Below are the some of the examples of retrieving data in Sql Server using PIVOT and Dynamic PIVOT:
- How to get Hourly data in Sql Server
- How to get Daily data in Sql Server
- How to get Monthly Data in Sql Server
- How to get Quarterly Data in Sql Server
- How to get Yearly data in Sql Server
[ALSO READ]
PIVOT and UNPIVOT in Sql Server
Very useful. Keep it up. Thanks a lot.
Thanks… Nice article. Help me do my work..
As i have null values i need to use ISNULL() function while sum, which i am not able to add. without this values for column level calculating as zero.
Pls. suggest
Any suggestion on how to get the order of the resulting pivot to be alphabetical
How do I create the pivot table as a temp table that I can join to other tables?
i need add where year= ‘2012’
utiliser Having year=”2012”
declare c cursor for select distinct annee from ventesSajid order by annee
declare @pivot varchar(200), @sum varchar(200), @sql nvarchar(1000), @col int
select @pivot=”, @sum=”
open c
fetch c into @col
while @@FETCH_STATUS = 0
begin
select @sum = @sum + ‘ SUM([‘+ cast(@col as varchar(30)) +’]) AS [‘+ cast(@col as varchar(30)) +’],’,
@pivot = @pivot + ‘ [‘+ cast(@col as varchar(30)) +’], ‘
fetch c into @col
end
close c
deallocate c
set @sql = ‘SELECT Produit, ‘+ left(@sum, len(@sum)-1)+
‘ FROM ventesSajid PIVOT (SUM(Vente)
FOR annee IN (‘+ left(@pivot, len(@pivot)-1)+ ‘)) AS Annees
GROUP BY Produit Having Produit = ”A” or Produit = ”B” ‘
exec(@sql)
Very Nice and Very useful. Keep it up. Thanks a lot.
I am trying to do a similar pivot table but I want to swap the rows and columns. I would like the Year to be dynamic. I currently have all my records with a datatype Datetime but only want to view year.
I can get it working without dynamic columns but want to make the years dynamic, I am having problems I think converting the date into a year and then using it.
Any suggestions would be appreciated.
Thanks,
SELECT *
FROM (
SELECT Convert(varchar(4),Year(AsAtDate)) as ‘YR’
,I.ItemDesc as ‘Benchmark Name’
,CalculationValue
FROM tBJBenchmarkStatistics as BS
inner join tSystemCodes as I
on BS.BenchmarkID = I.ItemID
WHERE (Year(ASATDATE) = Year(SYSDATETIME())
or Year(ASATDATE) = Year(SYSDATETIME())-1
or Year(ASATDATE) = Year(SYSDATETIME())-2
or Year(ASATDATE) = Year(SYSDATETIME())-3
or Year(ASATDATE) = Year(SYSDATETIME())-4
or Year(ASATDATE) = Year(SYSDATETIME())-5
)
and Month(AsAtDate) = 12
and BS.CalculationType = 50301
and BS.MonthCount = 12
and BS.BenchmarkID in (2001,2003,2205,2019,2204,2203,2006,2047,2022)
) as TableData
PIVOT (
Sum(CalculationValue)
FOR Yr in ([2013],[2012],[2011],[2010],[2009])
) as PivotTable
nice article,sharp knowledge by reading this article…
Its very useful but instead of in dynamic query we have used nvarchar variable @ColumnName instead of this may i able to use Temporary Tables like
(select columnname from #Temp1)
Nice Article……………….
thank you so mush , it’s very helpful 🙂
It took me a while to figure most of it out. Compact code and works beautifully.
One question: how can I include a ISNULL for replacing the “aggregated” as blank instead of NULL?
sorry i didnt get you question…use ‘coalesce’
Thank you for this post, very useful and amazing. From Oracle DBase here now working on SQL Server. =)
Muchas gracias, excelente ejemplo!! 😀
Very Help Full, Thanks a lot,, Keep it up…
Must declare the scalar variable “@DynamicPivotQuery”.
i got error like this what i do
must be some typographic errors or you may not declare it in nvarchar..
I executing below query, where error is ‘Incorrect Syntax Near Pivot’
go
with taxdet as
(Select tbi.bill_item_id,tbi.Sr_number, tbi.item_id,tbi.total_amt, tbt.tax_amt, tbt.sub_tax_id,
tbm.Bill_no, tbm.Bill_amount, tbm.current_table_no, tstm.SubTax_Name
from tblBill_Items tbi
Right Join tblBill_taxes tbt on tbt.Corelation_id = tbi.bill_item_id
Right Join tblBill_master tbm on tbm.Sr_number = tbi.Sr_number
Left Join tblSubTax_master as tstm on tstm.SubTax_id = tbt.sub_tax_id
where tbt.tax_id = 1 and tbm.isSettle = 1 and tbm.Bill_no > 0)
Select * from taxdet
— Select * into DPKTAX from (Select * from Taxdet)
set nocount on
Declare @DynamicPivotquerry as nvarchar(max)
Declare @columnname as nvarchar(max)
Select @columnname = isnull (@columnname + ‘,’,”)+QUOTENAME([SubTax_Name]) from (Select Distinct[SubTax_Name]
from [tblSubTax_master]) as [subtax_name]
Set @DynamicPivotquerry = N’ with DPKTax as (Select * from Taxdet)
Pivot(sum(Tax_amt) for subtax_name in (‘ + @columnname + ‘)) as PVT’
exec sp_executesql @dynamicpivotquerry
Please help
this is great but,
this is not dynamic at all, because at the top of the query you are trying to find the pivot columns and writting these columns hardcoded so this is not exactly dynamic but half dynamic :)) but however this is great post thanks.
We Can eliminate Null values in the pivoted list as below
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX), @ColumnNamesFOR AS NVARCHAR(MAX)
–Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ‘,’,”)
+ ‘isnull(‘ +QUOTENAME(Subclient) + ‘,0) as ‘ + QUOTENAME(Subclient)
FROM (SELECT DISTINCT Subclient FROM ##temp) AS Subclients
SELECT @ColumnNamesFOR= ISNULL(@ColumnNamesFOR + ‘,’,”)
+ QUOTENAME(Subclient)
FROM (SELECT DISTINCT Subclient FROM ##temp) AS Subclients
select @ColumnName,@ColumnNamesFOR
–Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
N’SELECT [Reason Code], ‘ + @ColumnName + ‘
FROM ##temp
PIVOT(SUM(total)
FOR Subclient IN (‘ + @ColumnNamesFOR + ‘)) AS PVTTable’
–Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery
thanks,
jasmine
Dear,
You save my day.
Thanks.
Thank you Prateek
DECLARE @ColumnName NVARCHAR(MAX), @DynamicPivotQuery NVARCHAR(MAX);
–Get distinct values of the PIVOT Column
SELECT @ColumnName= COALESCE(@ColumnName + ‘,’,”)
+ QUOTENAME(floor_name)
FROM (select DISTINCT b.floor_name from tbl_roomno as a inner join tbl_floortype as b on a.floor_id=b.floor_id) AS Courses
–Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
N’SELECT a.status, ‘ + @ColumnName + ‘
tbl_roomno as a inner join tbl_floortype as b on a.floor_id=b.floor_id
PIVOT(SUM(a.room_no)
FOR b.floor_name IN (‘ + @ColumnName + ‘)) AS PVTTable’
–Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword ‘as’.
Thanks tut,
So i try this but my solution useful
1. My data here:
2. My script:
———–
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
–Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ‘,’,”)
+ QUOTENAME(MON_Name)
FROM (SELECT DISTINCT MON_Name FROM ListForCapChungChi where DIE_LOPID = 7) AS Courses
–Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
N’SELECT DISTINCT(HOV_ID) As HOV_ID, HOV_LastName, DIE_LOPID, MON_ID, ‘ + @ColumnName + ‘
FROM ListForCapChungChi
PIVOT(SUM(DIE_Diem)
FOR MON_Name IN (‘ + @ColumnName + ‘)) AS PVTTable Where DIE_LOPID= 7′–+CONVERT(nvarchar, @DIE_LOPID)
+’ AND DIE_Active= 1 ‘–+CONVERT(nvarchar,@DIE_Active)
–Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery
—————-
3. My result:
4. So i want to my result that:
Please help me.
i have one table contain two columns one is Type_of_case and other is Stage Both
i want to prepare lookup table
i tried your example its working ok but i am un able to make it for my table
heres the list of all colums for instance
SELECT TOP 1000 [Case_id]
,[District]
,[WM_Office]
,[WM_Complaint_no]
,[Complainant_name]
,[Tracking_ID]
,[Retained_CNIC]
,[Sur_CNIC]
,[Subject]
,[Type_of_case]
,[Date_of_registration]
,[Disposal_Date]
,[WM_Regulation]
,[Attended_by]
,[Hearing_dt]
,[Stage]
,[Ticket_no]
,[Remarks]
FROM [WM_Cases].[dbo].[Cases_main]
i need this result
Type_of_case Total Of Case_id Case Closed Closure Finding Received
Admin 6 1 5
Age 70 1 65 4
Decipline 54 2 35 8
Thank you for a very clear and precise article. I used the logic and I am almost getting what I need. I need a slight variation. Here is the Dynamic Pivot
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ThirdPartyPayer AS NVARCHAR(MAX)
Select @ThirdPartyPayer= coalesce(@ThirdPartyPayer +’,’,”) + QUOTENAME(ThirdPartyPayerPL)
From (Select distinct ThirdPartyPayerPL
From ##PLTPNEW) as PLPT
SET @DynamicPivotQuery =
N’SELECT [Patient I.D. Number – PL],[Document Number – PL],[Claim Line Number – PL]
,’ + @ThirdPartyPayer + ‘
From ##PLTPNEW
Pivot(sum(PaidAmountPL) For ThirdPartyPayerPL in (‘ + @ThirdPartyPayer + ‘)) as PLPIVOT’
EXEC sp_executesql @DynamicPivotQuery
and here is the result (I am shortening field lables)
Patient ID Document Number Claim N CARE U007
1000 289 1 76.58 19.53
However I need the result be like:
Patient ID Document Number Claim N Insu1 Insu2 Paid1 Paid2
01000 289 1 CARE U007 76.58 19.53
Any suggestions?
Thank You
Thank you very much , I got what I wanted. Thanks again.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘(‘.
When i execute with my table it comes this error. I cant identified the error.
I got the same error, did you ever get it resolved? Thanks!
My query is working successfully, but some of the columns are not showing up. For instance, in the “Name” Column, I have the values “Object”, “Use”, and “Size” that I am trying to use as column headers, but only the Use and Size are appearing as headers. I can see Object as a value in the table I’m pivoting from, so I don’t know why it’s hidden. Can anyone help?
hi,
how to set order by in dynamic pivot query.
please advise.
thanks.
hi
thanks
Thank you Samad… Appreciate your comments…
Thank you very much. I try it and it works fine. What I need now Is to add Grand total Column that sum all Earning for each year. thank you for help.
This was supremely useful to me. Thank you!
Excellent!
It helps me a lot to serve my customer.
Thanks Anjan… Appreciate your comments…
thank it very nice solution but i want to use where clause
Finally your Solution Helps me…
Thank You
Very Nice.
Helped me alot and time saving.
This is by far the best article that I have read on the subject. Others weren’t as clear and precise
This was very helpful thank you! Would anyone know how to create these parameters in SSRS as well? I have gotten the query to work successfully in SQL but I am now trying to create the report in SSRS and am not sure how to set the parameters.
Thanks for help, you saved me time