Dynamic PIVOT in Sql Server

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:

Table to be Pivoted in Sql

--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

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:
Static PIVOT Query In Sql Server

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:
Static PIVOT Query In Sql Server

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:
Dynamic PIVOT Query in Sql Server

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:

[ALSO READ]
PIVOT and UNPIVOT in Sql Server

53 thoughts on “Dynamic PIVOT in Sql Server

  1. 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

    1. 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)

  2. 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

  3. 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)

  4. 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?

  5. 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

  6. 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.

    1. 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

  7. 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’.

  8. 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.

  9. 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

  10. 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

  11. 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?

  12. 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.

  13. This is by far the best article that I have read on the subject. Others weren’t as clear and precise

  14. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *