CODEWITHSUNDEEP
sql-serverpivot
Sanjay_Umeda
Sanjay_Umeda

Reputation: 119

Sql Server Pivot Rows Into Columns with NULL Values As Column wtih default name

I want to Pivot Rows into dynamic Columns which is working fine but the issue is NULL values in rows does not transformed into columns with any default name and instead of NULL I want ZERO(0) in value.

Please refer to this image for output :

:

Below is my sql query with table :

CREATE TABLE #CourseSales
 (Course VARCHAR(50) null,Year INT,Earning MONEY null)
 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)
 INSERT INTO #CourseSales VALUES(null,2013,1200)
 INSERT INTO #CourseSales VALUES(null,2012,null)

 select * from #CourseSales

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

drop table #CourseSales

Any help would be appreciated. Thanks.

Upvotes: 2

Views: 1677

Answers (1)

Tien Nguyen Ngoc
Tien Nguyen Ngoc

Reputation: 1555

You can try this

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 ISNULL(Course,'ZAny') AS 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 ISNULL(Course,'ZAny') AS Course FROM #CourseSales) AS Courses

--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =N'SELECT Year, ' + @PivotSelectColumnNames + ' FROM (SELECT ISNULL(Course,''ZAny'') AS Course,Year,Earning FROM #CourseSales) #CourseSales  PIVOT(SUM(Earning) FOR Course IN (' + @PivotColumnNames + ')) AS PVTTable'

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

Upvotes: 2

Related Questions