CODEWITHSUNDEEP
sqlsql-serverpivot-table
Rohan Rao
Rohan Rao

Reputation: 2603

Show 'P' instead of NULL values in PIVOT table

I am working on a PIVOT data and I am stuck at one point. This is my procedure:

ALTER PROCEDURE [dbo].[usp_GetLeaveReportOrg]
@startDate date,
@endDate date

AS
BEGIN
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @leaveReport TABLE (
    BuddyId bigint,
    Name nvarchar(300),
    RoleName nvarchar(150),
    DivisionName nvarchar(200),
    LeaveTypeName nvarchar(max),
    DateFrom date,
    DateTo date
 )

 insert into @leaveReport
 select tbl_Lm.BuddyId, tbl_Bm.Name,tbl_Rm.RoleName, tbl_Dm.DivisionName,tbl_Lt.LeaveTypeName,tbl_Lm.DateFrom, tbl_Lm.DateTo from tbl_LeaveMaster as tbl_Lm 
inner join tbl_BuddyMaster as tbl_Bm on tbl_Bm.id= tbl_Lm.BuddyId inner join tbl_DivisionMaster as tbl_Dm on tbl_Dm.Id = tbl_Bm.Divisionid 
inner join tbl_LeaveType as tbl_Lt on  tbl_Lt.Id = tbl_Lm.LeaveTypeId inner join tbl_RoleMaster as tbl_Rm on tbl_Lt.Id = tbl_Rm.Id

select BuddyId,Name,  RoleName , DivisionName,DateFrom, isnull(LeaveTypeName,'P') temp into #PivotLeaveData from @leaveReport where
CAST (DateFrom as date) >= cast(@startDate as date) and 
cast(DateTo as date) <= cast(@endDate as date)
order by DateFrom  ASC 

SELECT @ColumnName =COALESCE (@ColumnName + ',[' + CONVERT(NVARCHAR, [DateFrom], 106) + ']', 
           '[' + CONVERT(NVARCHAR, [DateFrom], 106) + ']')
           FROM    (SELECT DISTINCT [DateFrom] FROM #PivotLeaveData) PV  
           ORDER BY [DateFrom]

 SET @DynamicPivotQuery = 
N'SELECT BuddyId,Name,RoleName,DivisionName, ' + @ColumnName + '
FROM #PivotLeaveData
PIVOT(Max( [LeaveTypeName]   ) 
      FOR [DateFrom] IN (' + @ColumnName + ')) AS PVTTable'

EXEC(@DynamicPivotQuery)

END

PROBLEM:

What I want is, I want to display 'P' for the LeaveTypeName if entry is null. That means, the employee is present at that day. When I am using isNull it showing NULL instead of P.

WHAT I TRIED:

EDIT: Here is my sample data:

BuddyId   Name  RoleName    DivisionName   01Jan2020     02Jan2020    ....
1          ABC  Developer    Dev              NULL      Casual Leave
2          DEF  Maintenance  Maintenance    Sick Leave   NULL

WHAT I NEED: Replace the Nulls with 'P'

Where I am going wrong?

Upvotes: 0

Views: 111

Answers (2)

Atk
Atk

Reputation: 752

use another variable to handle null data

ALTER PROCEDURE [dbo].[usp_GetLeaveReportOrg]
@startDate date,
@endDate date

AS
BEGIN
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @ShowColumnName AS NVARCHAR(MAX)
DECLARE @leaveReport TABLE (
    BuddyId bigint,
    Name nvarchar(300),
    RoleName nvarchar(150),
    DivisionName nvarchar(200),
    LeaveTypeName nvarchar(max),
    DateFrom date,
    DateTo date
 )

 insert into @leaveReport
 select tbl_Lm.BuddyId, tbl_Bm.Name,tbl_Rm.RoleName, tbl_Dm.DivisionName,tbl_Lt.LeaveTypeName,tbl_Lm.DateFrom, tbl_Lm.DateTo from tbl_LeaveMaster as tbl_Lm 
inner join tbl_BuddyMaster as tbl_Bm on tbl_Bm.id= tbl_Lm.BuddyId inner join tbl_DivisionMaster as tbl_Dm on tbl_Dm.Id = tbl_Bm.Divisionid 
inner join tbl_LeaveType as tbl_Lt on  tbl_Lt.Id = tbl_Lm.LeaveTypeId inner join tbl_RoleMaster as tbl_Rm on tbl_Lt.Id = tbl_Rm.Id

select BuddyId,Name,  RoleName , DivisionName,DateFrom, LeaveTypeName into #PivotLeaveData from @leaveReport where
CAST (DateFrom as date) >= cast(@startDate as date) and 
cast(DateTo as date) <= cast(@endDate as date)
order by DateFrom  ASC 

SELECT @ColumnName =COALESCE (@ColumnName + ',[' + CONVERT(NVARCHAR, [DateFrom], 106) + ']', 
           '[' + CONVERT(NVARCHAR, [DateFrom], 106) + ']')
           FROM    (SELECT DISTINCT [DateFrom] FROM #PivotLeaveData) PV  
           ORDER BY [DateFrom]

SELECT @ShowColumnName=COALESCE (@ColumnName + ',ISNULL([' + CONVERT(NVARCHAR, [DateFrom], 106) + '],''P'') as '+'[' + CONVERT(NVARCHAR, [DateFrom], 106) + ']', 
           'ISNULL([' + CONVERT(NVARCHAR, [DateFrom], 106) + '],''P'') as '+'[' + CONVERT(NVARCHAR, [DateFrom], 106) + ']')
           FROM    (SELECT DISTINCT [DateFrom] FROM #PivotLeaveData) PV  
           ORDER BY [DateFrom]


 SET @DynamicPivotQuery = 
N'SELECT BuddyId,Name,RoleName,DivisionName, ' + @ShowColumnName+ '
FROM #PivotLeaveData
PIVOT(Max( [LeaveTypeName]   ) 
      FOR [DateFrom] IN (' + @ColumnName + ')) AS PVTTable'

EXEC(@DynamicPivotQuery)

END

Upvotes: 1

Harlo
Harlo

Reputation: 507

try this

DECLARE @ColumnNameNOTnull AS NVARCHAR(MAX)

SELECT @ColumnNameNOTnull =
            STUFF(
            (
                SELECT ', ISNULL(' + QUOTENAME([DateFrom]) + ', ''P'') AS ' + QUOTENAME([DateFrom])
                FROM (SELECT DISTINCT [DateFrom] FROM #PivotLeaveData) tempTBL
                ORDER BY [DateFrom]
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
            ,1 , 1, '')

 SET @DynamicPivotQuery = 
        N'SELECT BuddyId,Name,RoleName,DivisionName, ' + @ColumnNameNOTnull + '
        FROM #PivotLeaveData
        PIVOT(Max( [LeaveTypeName]   ) 
              FOR [DateFrom] IN (' + @ColumnName + ')) AS PVTTable'

Upvotes: 0

Related Questions