Right Click Is Disabled...

Pivot Table From Sql Server

How To Return A Pivot Table From Sql Server. 

What is a pivot table and why to use Pivot Table?

PivotTable is an interactive way and very quickly way to summarize all the large amounts of data which are on daily bases. You can use a PivotTable to analyze numerical data in detail, and answer unanticipated questions about your data. PivotTable is especially made for desiging the  Querying in a very large amounts of data and in a simple and user-friendly ways.

So, we can summarized are data very quickly in a  simple way.

What is pivot table example?

A Pivot table is a table of stats which summarizes the data as sums, averages, and many other statistical measures. Let’s assume that we got data of any real estate project with different fields like type of flats, block names, area of the individual flats, and their different cost as per different services, etc.

Why I wrote this article?

I have been searching a long for making a pivot table with dynamic columns, but I was not able to find.

We have to specify the name of column, so that column will appear in that pivot table.

So for this reason, I am writing this blog so other may get some help from it.

How do you create a pivot table with static columns?

Let us take a look at an example of Vehicle table.

In this example we will get the distance for vehicles for specific dates.

In this table it consists off Vehicle Id, Vehicle Name, Daily Vehicle Distance, Start Date and End Date of Distance Covered. 

CREATE TABLE [dbo].[Vehicles](

            [Id] [int] NULL,

            [VehicleId] [int] NULL,

            [VehicleName] [varchar](50) NULL,

            [StartDate] [datetime] NULL,

            [EndDate] [datetime] NULL,

            [Distance] [float] NULL


Below is the query which returns distance for vehicles for specific dates:

Declare @StartDate DATETIME,@EndDate DATETIME,@cols AS NVARCHAR(MAX) 

SELECT @StartDate = ‘2020-12-31’, @EndDate = ‘2021-01-05’; 



SELECT VehicleId ,VehicleName, StartDate,Distance FROM [vivek].[dbo].[Vehicles] 

WHERE StartDate  BETWEEN  @StartDate   AND @EndDate 

) as SourceTable 



max(Distance) FOR  StartDate in   





Result for above query looks like below,

How do you create a pivot table with dynamic columns?

Let us take a look at an example. In this example we will get the distance for vehicles for dynamic dates.

As we need dates as dynamic columns,

we have to get list of distinct dates from table based upon filter criteria. We then concatenate comma separate dates as string.

Next step is to create an dynamic query which use the dynamic columns which we prepared as a string.

Final step is to execute the dynamic query using sp_executesql statement.

Declare @StartDate DATETIME,@EndDate DATETIME

SELECT @StartDate = ‘2020-12-31’, @EndDate = ‘2021-01-05’; 


WITH ListDates(AllDates) AS

(    SELECT @StartDate AS DATE



    FROM ListDates 

    WHERE AllDates < @EndDate)

SELECT AllDates   into   #table FROM ListDates 



select @cols = STUFF((SELECT ‘,’ + QUOTENAME(convert(char(10), AllDates, 120)) 

                    from #table

                    group by AllDates

                    order by AllDates 

            FOR XML PATH(”), TYPE

            ).value(‘.’, ‘NVARCHAR(MAX)’) 


                        SET @sql = ‘select * from (SELECT VehicleId ,VehicleName, StartDate,Distance FROM [vivek].[dbo].[Vehicles]   


            pivot ( max(Distance) for  StartDate in    (‘ + @cols + ‘)) piv ORDER BY VehicleId’

EXEC Sp_executesql @sql 



The Result Will looks like below:

Share On

Leave a Comment

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