Query for casting bit datatype to nvarchar [Resolved]

Posted by Rasagna under Sql Server on 12/22/2015 | Points: 10 | Views : 332 | Status : [Member] | Replies : 4
Hii,
I have a table named Details which contains one of the columns named category which has to display 'cat-x' or 'cat-y' but that column is of bit datatype.I wanted to display actual value representing bit datatype(say if bit value is 1 display 'cat-x' if 0 display 'cat-y') instead of displaying bit datatype when displaying the whole table data to the user.Can any one help me with the query to display Details table with category column displaying 'cat-x' or 'cat-y' ?




Responses

Posted by: Professionaluser on: 12/22/2015 [Member] [MVP] Bronze | Points: 50

Up
2
Down

Resolved
We can concert BIT values to specific NVARCHAR data using CASE statement
Method-1:

declare @Details table(ID Int identity(1,1),Category bit)
insert @Details values(0),(1),(1)
SELECT ID, Category, Case WHEN Category = 0 then 'cat-x' WHEN Category = 1 then 'cat-y' end CategoryforDisplay
FROM @Details

/*
ID Category CategoryforDisplay
1 0 cat-x
2 1 cat-y
3 1 cat-y
*/


Method-2:
check the below code snippet for assigning BIT value to NVARCHAR variable
DECLARE @NEWVALUE NVARCHAR(30)
CREATE TABLE #tempBit (bitValue BIT NOT null)
INSERT INTO #tempBit VALUES(1)

SELECT @NEWVALUE = bitValue FROM #tempBit
SELECT @NEWVALUE

DROP TABLE #tempBit


Rasagna, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajnilari2015 on: 12/22/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
1
Down

Resolved
@Rasagna Sir, below are some of the ways to do so

DECLARE @Details table(Name VARCHAR(10),Category BIT)

INSERT @Details VALUES('Name1',1),('Name2',1),('Name3',0),('Name4',1),('Name5',0)


--Way 1 : Using Case statement

SELECT

Name
,Category
,NewCategory = CASE WHEN Category = 1 THEN 'cat-x' ELSE 'cat-y' END
FROM @Details


--Way 2: Using IIF function

SELECT

Name
,Category
,NewCategory = IIF(Category = 1,'cat-x','cat-y')
FROM @Details


-- Way 3: Using Choose function
SELECT

Name
,Category
,NewCategory = CHOOSE(CASE WHEN Category = 1 THEN 1 ELSE 2 END,'cat-x','cat-y')
FROM @Details


In all the above case the result will be

Name	Category	NewCategory

Name1 1 cat-x
Name2 1 cat-x
Name3 0 cat-y
Name4 1 cat-x
Name5 0 cat-y


Hope that helps.

--
Thanks & Regards,
RNA Team

Rasagna, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rasagna on: 12/22/2015 [Member] Starter | Points: 25

Up
1
Down

Resolved
@ Rajnilari2015 ,@Professionaluser Thanks for your replies and sorry for framing my question wrong as for bit it is 0 and 1 not true and false.
I have got answer for this
SELECT Item_Id,Product,Case WHEN Category = 1 then 'Cat-X'  WHEN Category = 0 then 'Cat-Y' end as Category,DateOfManufacture FROM Details


Rasagna, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajnilari2015 on: 12/22/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

Up
0
Down
@Rasagna Sir, glad that it helped you (:

--
Thanks & Regards,
RNA Team

Rasagna, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response