Need comma separated output
-
Create Table #Test
(id int identity,C1 bit , C2 Bit , C3 Bit)
insert into #test Values (0,0,0)
insert into #test Values (0,1,0)
insert into #test Values (0,1,1)
insert into #test Values (1,0,1)
insert into #test Values (1,1,1)
If C1 = 1 then replace with A
If C2 = 1 then replace with B
If C3 = 1 then replace with C
And need output in comma separated like follows
http://www.sqlservercentral.com/Forums/Attachment18094.aspx%5B/img%5D
-
what have you tried?
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
SELECT id,
[Output] = ISNULL(STUFF(
CASE C1 WHEN 1 THEN ',A' ELSE '' END +
CASE C2 WHEN 1 THEN ',B' ELSE '' END +
CASE C3 WHEN 1 THEN ',C' ELSE '' END
,1,1,SPACE(0)),'')
FROM #Test
CASE replaces 1s with the corresponding character A,B,C
STUFF strips away any extra trailing comma
ISNULL takes care of the empty output for the case when C1,C2 and C3 are all 0s.
-- Gianluca Sartori
-
Quick suggestion
😎
USE tempdb;GO
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#Test') IS NOT NULL DROP TABLE #Test;
Create Table #Test
(id int identity,C1 bit , C2 Bit , C3 Bit)
insert into #Test Values (0,0,0)
,(0,1,0)
,(0,1,1)
,(1,0,1)
,(1,1,1);
SELECT
T.id
,CONCAT
(
CHAR(NULLIF(CONVERT(TINYINT,T.C1,0),0) + 64) + CHAR(44)
,CHAR(NULLIF(CONVERT(TINYINT,T.C2,0),0) + 65) + CHAR(44)
,CHAR(NULLIF(CONVERT(TINYINT,T.C3,0),0) + 66)
) AS OUT_STR
FROM #Test T;
Results
id OUT_STR----------- -------
1
2 B,
3 B,C
4 A,C
5 A,B,C
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply