How to display sql server boolean type True & False for 1 & NULL ?
In my query I am able to get column values as 1 and NULL. I would like to display True & False for 1 & NULL? /*Declare Variable*/ DECLARE @Pivot_Column [nvarchar](max); DECLARE @Query [nvarchar](max); /*Select Pivot Column*/ SELECT @Pivot_Column= COALESCE(@Pivot_Column+',','')+ QUOTENAME(PracticeLocationServiceCode) FROM (SELECT DISTINCT [PracticeLocationServiceCode] FROM PracticeLocationService)Tab /*Create Dynamic Query*/ SELECT @Query=' ;WITH ServiceOffered AS ( SELECT PracticeLocationID, '+@Pivot_Column+'FROM (SELECT PracticeLocationID, PracticeLocationServiceCode,CAST(ServiceFlag AS INT) ServiceFlag FROM PracticeLocationService )Tab1 PIVOT ( MAX(ServiceFlag) FOR [PracticeLocationServiceCode] IN ('+@Pivot_Column+')) AS Tab2 ) SELECT REGION,LocationShortName,GLCODE,C.* FROM ServiceOffered C INNER JOIN VWLOCATIONS V ON V.PracticeLocationID = C.PracticeLocationID ORDER BY REGION,LocationShortName ' /*Execute Query*/ EXEC sp_executesql @Query Does SQL Server have Boolean data type?
SQL server boolean data type=can be TRUE , FALSE or UNKNOWN . However, the boolean data type is only the result of a boolean expression containing some combination of comparison operators (e.g. = , <> , < , >= ) or logical operators (e.g. AND , OR , IN , EXISTS ).
Alternatively, you can use strings 'true' and 'false', but these are strings just like any other string. Often the bit type is used instead of Boolean as it can only have values 1 and 0. Typically 1 is used for "true" and 0 for "false". It is still an integer type, though. Boolean logic will not work. For example, if (CONVERT(bit, 1) and CONVERT(bit, 1))... is not legal syntax.Likely the best option is to use a CASE and return the requisite strings, as @Akina commented: CASE WHEN TheColumn = 1 THEN 'True' WHEN TheColumn IS NULL THEN 'False' ELSE 'Oops!' END