Tag Archives: PIVOT

How to replace NULL value by 0 in the Dynamic Pivot result – Sql Server

Sql Server, , , , , , , , ,

For the previous articles PIVOT and UNPIVOT in Sql Server and Dynamic PIVOT in Sql Server, recieved couple of comments requesting: how to replace NULL value by 0 in the PIVOT result?. In this article let us understand replacing NULL values in the PIVOT result with an example.

PROBLEM

Below is the pictorial explanation of this problem of NULL value in the PIVOT result:

Replace Null by Zero PIVOT result Sql Server 1

ScriptBelow is the script to create the temporary table #CourseSales table with sample data as depicted in the above image:

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('.NET',2012,5000)
 INSERT INTO #CourseSales VALUES('Java',2012,20000)
 INSERT INTO #CourseSales VALUES('Java',2013,30000)
 GO

Below is the Dynamic PIVOT script to generate the PIVOT result with NULL value as shown in the above image:

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
 GO

RESULT of the above query:
Replace Null by Zero PIVOT result Sql Server 2

SOLUTION

We can modify the previous query like below to replace NULL by 0 in the dynamic PIVOT query result:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX),
        @PivotColumnNames AS NVARCHAR(MAX),
        @PivotSelectColumnNames AS NVARCHAR(MAX)
--Get distinct values of the PIVOT Column
SELECT @PivotColumnNames= ISNULL(@PivotColumnNames + ',','')
+ QUOTENAME(Course)
FROM (SELECT DISTINCT Course FROM #CourseSales) AS Courses
--Get distinct values of the PIVOT Column with isnull
SELECT @PivotSelectColumnNames 
	= ISNULL(@PivotSelectColumnNames + ',','')
	+ 'ISNULL(' + QUOTENAME(Course) + ', 0) AS ' 
	+ QUOTENAME(Course)
FROM (SELECT DISTINCT Course FROM #CourseSales) AS Courses
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
N'SELECT Year, ' + @PivotSelectColumnNames + '
FROM #CourseSales
PIVOT(SUM(Earning)
FOR Course IN (' + @PivotColumnNames + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

RESULT:
Replace Null value by Zero in the Dynamic PIVOT result Sql Server 3

[ALSO READ] PIVOT and UNPIVOT in Sql Server
Dynamic PIVOT in Sql Server

Dynamic PIVOT in Sql Server

Sql Server, Sql Server 2005, , , , , , ,

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

PIVOT and UNPIVOT in Sql Server

Sql Server, Sql Server 2005, , , , , , ,

This article provides introduction to the PIVOT and UNPIVOT relational operators introduced in Sql Server 2005 with extensive list of examples.

ALSO READ: Dynamic PIVOT in Sql Server

PIVOT

PIVOT is one of the New relational operator introduced in Sql Server 2005. It provides an easy mechanism in Sql Server to transform rows into columns.

To understand PIVOT with extensive list of examples, let us 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

Example 1:

In this example the #CourseSales Table data is Pivoted so that the Course becomes the column headings.

Pivot Example 1 In Sql Server

Below 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

Example 2:

In this example the #CourseSales Table data is Pivoted so that the Year becomes the column headings.

Pivot Example 2 In Sql Server

Below script pivots the #CourseSales Table data so that the Year columns distinct values are transformed as Columns in the result set as depicted in the above image.

SELECT *
FROM #CourseSales
PIVOT(SUM(Earning) 
	  FOR Year IN ([2012],[2013])) AS PVTTable

Example 3:

Transforming Sales Table data to a Quarterly aggregated sales data with Quarters as the Columns in the result set.
Pivot Example 3 In Sql Server

Let us create a Temporary Sales table and insert 1000 Sample Sales Records With Random past 0-1000 days as the sales date by using the below script.

--Create Temporary Sales Table
CREATE TABLE #Sales
(SalesId INT IDENTITY(1,1), SalesDate DateTime)
GO
--Populate 1000 Sample Sales Records With 
--Random past 0-1000 days as sales date
INSERT INTO #Sales(SalesDate)
VALUES(DATEADD(dd, - CONVERT(INT, (1000+1)*RAND()),GETDATE()))
GO 1000

Now we can write a query like below which Pivots the Sales Table Data such that the quarters are the columns in the final result set as depicted in the above image.

SELECT Year, QPivot.[1] As Q1, QPivot.[2] As Q2, 
 QPivot.[3] As Q3, QPivot.[4] As Q4
FROM (SELECT YEAR(SalesDate) [Year],
     DATEPART(QUARTER, SalesDate) [Quarter], 
         COUNT(1) [Sales Count]
      FROM #Sales
      GROUP BY YEAR(SalesDate), 
         DATEPART(QUARTER,SalesDate)) AS QuarterlyData
PIVOT( SUM([Sales Count])   
        FOR QUARTER IN ([1],[2],[3],[4])) AS QPivot

Example 4:

Transforming Sales Table data to a Monthly aggregated sales data with Months as the Columns in the result set.
Monthly Data Using Pivot In Sql Server 2005

We can write a PIVOT query like below to get the Monthly Data as depicted in the above image.

SELECT *
FROM (SELECT YEAR(SalesDate) [Year], 
       DATENAME(MONTH, SalesDate) [Month], 
       COUNT(1) [Sales Count]
      FROM #Sales
      GROUP BY YEAR(SalesDate), 
      DATENAME(MONTH, SalesDate)) AS MontlySalesData
PIVOT( SUM([Sales Count])   
    FOR Month IN ([January],[February],[March],[April],[May],
    [June],[July],[August],[September],[October],[November],
    [December])) AS MNamePivot

[ALSO READ] How to replace NULL value by 0 in the Pivot result

UNPIVOT

UNPIVOT is the reversal of the PIVOT operation. It basically provides a mechanism for transforming columns into rows.

UNPIVOT Example in Sql Server

From the above image it is clear that UNPIVOT is the reversal of the PIVOT operation. But it is not the exact reversal of PIVOT operation as PIVOT operation generates the aggregated result, so UNPIVOT will not be able to split the aggregated result back to the original rows as they were present prior to the PIVOT operation.

As depicted in the above image there were 5 rows in the #CourseSales Table Prior to PIVOT, but a PIVOT and it’s reversal UNPIVOT resulted in 4 rows only. The reason for this is for .NET Course in Year 2012 there were two records one with earning 10K and another with earning 5K, the PIVOT and it’s reversal UNPIVOT result last lost this split information and instead of two rows it has generated one row for the .NET course in Year 2012 with Earning as sum of 10K and 5K i.e. 15K.

We can use the below script to simulate the PIVOT and UNPIVOT operation as depicted in the above image on the #CourseSales Temporary Table created in the beginning PIVOT section of this article.

--PIVOT the #CourseSales table data on the Course column 
SELECT *
INTO #CourseSalesPivotResult
FROM #CourseSales
PIVOT(SUM(Earning) 
	  FOR Course IN ([.NET], Java)) AS PVTTable
GO
--UNPIVOT the #CourseSalesPivotResult table data 
--on the Course column 	  
SELECT Course, Year, Earning
FROM #CourseSalesPivotResult
UNPIVOT(Earning
	  FOR Course IN ([.NET], Java)) AS UNPVTTable

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