make null value as X sql server

Question

i have problem.., i want to display the null value as 'X' i has been using the case statement but still failed, i already using Isnull and failed too.

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @X AS NVARCHAR(MAX)



SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(code)
FROM (SELECT DISTINCT code FROM edrsDB..tbl_users where area='west' ) AS status

SET @DynamicPivotQuery = 
N'SELECT date, '+ @ColumnName +' 
FROM (
SELECT DISTINCT userid, status , DATEPART(dd, [date]) as Date
FROM edrsDB..tbl_status
WHERE DatePart(MM, [Date]) = 3
 And DATEPART(YYYY, [Date]) = 2017
Union
SELECT DISTINCT userid, status , DATEPART(DD,[date]) as Date
FROM edrsDB..tbl_public_holiday
WHERE DatePart(MM, [Date]) = 3
And DATEPART(YYYY, [Date]) = 2017
Union
SELECT DISTINCT userid, status , DATEPART(DD,[Date]) as Date 
FROM edrsDB..tbl_station_weekend
WHERE DatePart(MM, [Date]) = 3
And DATEPART(YYYY, [Date]) = 2017
And date IS NOT NULL
 ) AS x 
 PIVOT
 (
max(status)
FOR userid IN (' + @ColumnName + ')
) AS PVTTable'
 EXEC sp_executesql @DynamicPivotQuery

this is ouput.. , i want null to be display as 'X'

output

`coalesce(column, 'X')` will return X if column is null. – jarlh Mar 30 '17 at 09:22 — jarlh, Mar 30 '17 at 09:22
how to insert this ... '+ @ColumnName +'? – Tahi Mar 31 '17 at 00:49 — Tahi, Mar 31 '17 at 00:49

score 1 · Accepted Answer · answered Mar 30 '17 at 09:23

1

You can use ISNULL

ISNULL(<some_column/some_stuff>, 'X')

'X' would be the value displayed instead of the first parameter if first parameter result is null

answered Mar 30 '17 at 09:23

beejm

2,381
1
10
19

how to insert '+ @ColumnName +' and set x ? what i done is like this – Tahi Mar 31 '17 at 00:50
ISNULL('+@Columnname+' , ''X'') AS '+@Columnname+' – Tahi Mar 31 '17 at 00:51
''X'' ,, i use single quote – Tahi Mar 31 '17 at 00:51

score 0 · Answer 2 · answered Mar 30 '17 at 09:25

0

Use ISNULL function in SQL Server :

SELECT ISNULL(Your_column,'X')

answered Mar 30 '17 at 09:25

Mansoor

4,061
1
17
27

done do like that ,, but i dont know why error ..., this is what id done – Tahi Mar 31 '17 at 00:32
ISNULL('+@Columnname +' , ''X'' ) As '+@Columnname+' – Tahi Mar 31 '17 at 00:33

score 0 · Answer 3 · edited May 23 '17 at 12:17

0

You can use isnull() or coalesce() in sql server.

select isnull(col,'X')

or

select coalesce(col,'X')

The main difference between the two is that coalesce() can support more than 2 parameters, and it selects the first one that is not null. More differences between the two are answered here.

select coalesce(col,col2,'X')

coalesce() is also standard ANSI sql, so you will find it in most RDBMS. isnull() is specific to sql server.

Reference:

edited May 23 '17 at 12:17

Community

1
1

answered Mar 30 '17 at 12:53

SqlZim

37,248
6
41
59

how to implement to my code..? i has been try but nothing happen – Tahi Mar 31 '17 at 00:52

make null value as X sql server

3 Answers3