Author name: vivek Jaiswal

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
) ON[PRIMARY]

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* FROM
(
    SELECT VehicleId, 
           VehicleName, 
           StartDate, 
           Distance 
    FROM [vivek].[dbo].[Vehicles]
    WHERE StartDate 
    BETWEEN @StartDate 
        AND @EndDate
) as SourceTable
PIVOT
(
    max(Distance) FOR StartDate in([2020-12-31],[2021-01-01],[2021-01-02])
)
AS PIVOTTABLE 
ORDER BY VehicleId

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

BEGIN
WITH ListDates(AllDates) AS
(   
    SELECT @StartDate AS DATE
    UNION ALL
    SELECT DATEADD(DAY,1, AllDates)
    FROM ListDates 
    WHERE AllDates < @EndDate
)
SELECT AllDates   
into   #table 
FROM ListDates 

DECLARE @cols AS NVARCHAR(MAX),
@sql AS NVARCHAR(MAX)
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)'
),1,1,'')
    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 
DROP TABLE #table
END

The Result Will looks like below: