Replace `0` with `N/A` or `-1` in pivot query result

I have Attendance table in which date and attendance is stored and I am passing date range in this query to display attendance report.

Now my question is how can I replace 0 (which I am getting as a output if the date passsed doesn’t match with the date inside the Attendance table) with N/A or -1 ?

SET @query = 'SELECT RollNo,FirstName,LastName, ' + @cols + ' from 
         (
            select S.RollNo,U.FirstName,U.LastName,
            D.startdate,
            convert(CHAR(10), startdate, 120) PivotDate
            from #tempDates D,Attendance A, Student S, UserDetails U
            where D.startdate = A.Date and A.EnrollmentNo=S.EnrollmentNo and A.EnrollmentNo=U.userID
        ) x
       pivot 
       (
            count(startdate)
            for PivotDate in (' + @cols + ')
       ) p '

Answer

You could use CASE as Brandon Miller suggested. Here’s another option – you can use NULLIF to replace a zero with a null value, and then replace any null value with N/A. You’ll need to create a 2nd variable to represent your columns in the select statement of your dynamic query. Here’s a full example with test data:

-- test data
create table #tempDates (startdate date)
create table Attendance (date date, enrollmentno int)
create table Student (rollno int, enrollmentno int)
create table UserDetails (FirstName varchar(10), LastName varchar(10), userid int)

insert into #tempDates values ('1/1/2018')
insert into Attendance values ('1/1/2018', 1)
insert into Student values (1, 1)
insert into UserDetails values ('J', 'S', 1)

declare @cols varchar(100) = '[2018-01-01],[2018-01-02]'
declare @cols_select varchar(500) = 'ISNULL(NULLIF(CAST([2018-01-01] AS VARCHAR(10)), ''0''), ''N/A'') AS [2018-01-01],ISNULL(NULLIF(CAST([2018-01-02] AS VARCHAR(10)), ''0''), ''N/A'') AS [2018-01-02]'

DECLARE @query nvarchar(max)
SET @query = 'SELECT RollNo,FirstName,LastName, ' 
    + @cols_select
    + 'from 
     (
        select S.RollNo,U.FirstName,U.LastName,
        D.startdate,
        convert(CHAR(10), startdate, 120) PivotDate
        from #tempDates D,Attendance A, Student S, UserDetails U
        where D.startdate = A.Date and A.EnrollmentNo=S.EnrollmentNo and A.EnrollmentNo=U.userID
    ) x
   pivot 
   (
        count(startdate)
        for PivotDate in (' + @cols + ')
   ) p '

EXEC sp_executesql @query

Outputs:

RollNo  FirstName   LastName    2018-01-01  2018-01-02
1       J           S           1           N/A

For fun, here’s a function you can use to convert the @cols variable to the @cols_select variable:

create function dbo.fn_convert_cols(@cols varchar(max)) returns varchar(max)
as
begin
    declare @col varchar(20)
    declare @cols_select varchar(max) = ''

    declare @idx int, @idx2 int
    select @idx = CHARINDEX('[', @cols), @idx2 = CHARINDEX(']', @cols)
    while @idx > 0 and @idx2 > 0
    begin
        select @col = SUBSTRING(@cols, @idx + 1, @idx2 - @idx - 1)
        select @cols_select += ',ISNULL(NULLIF(CAST([' + @col + '] AS VARCHAR(10)), ''0''), ''N/A'') AS [' + @col + ']' 
        select @cols = SUBSTRING(@cols, @idx2 + 1, len(@cols) - @idx2)
        select @idx = CHARINDEX('[', @cols), @idx2 = CHARINDEX(']', @cols)
    end

    select @cols_select = SUBSTRING(@cols_select, 2, len(@cols_select) - 1) 
    return @cols_select
end
go

So now you can just call the function when you’re building the query, like this:

SET @query = 'SELECT RollNo,FirstName,LastName, ' + dbo.fn_convert_cols(@cols)+ ' from