Select Comma Separate values in SQL server 2012

Question

Following is the table:

I am trying to get comma separated values where ColC is not Null

Output:

(Sql,My),(Dev,Prod)

I am using sql server 2012 tried COALESCE but failed. Please help.

Here the create and insert script:

create table T_MyIssue(ColA Varchar(50), ColB Varchar(50), ColC Varchar(50))

Insert Into T_MyIssue(ColA,ColB,ColC)
values('Sql','My','DB'),
('Server','MS',Null),
('Dev','Prod','Mongo')

score 3 · Accepted Answer · answered Dec 22 '16 at 08:18

3

Don't know exactly what you need, but one of these should do the job

DECLARE @tbl TABLE(ColA VARCHAR(10),ColB VARCHAR(10),ColC VARCHAR(10));
INSERT INTO @tbl VALUES
 ('Sql','My','DB')
,('Server','MS',NULL)
,('Dev','Prod','Mongo')

SELECT ISNULL(ColA,'') + ',' + ISNULL(ColB,'')
FROM @tbl AS t
WHERE ColC IS NOT NULL

The result

Sql,My
Dev,Prod

Or this

SELECT STUFF(
(
    SELECT ',('+ ISNULL(ColA,'') + ',' + ISNULL(ColB,'') + ')'
    FROM @tbl AS t
    WHERE ColC IS NOT NULL
    FOR XML PATH('')
),1,1,'')

Returns

(Sql,My),(Dev,Prod)

answered Dec 22 '16 at 08:18

Shnugo

66,100
9
53
114

Perfect this is what I was trying to achieve, need to go through "STUFF". Thanks – Pranay Deep Dec 22 '16 at 08:22
@PranayDeep `STUFF` is only needed to cut away the leading comma, The hard work is done by `FOR XML PATH('')` – Shnugo Dec 22 '16 at 08:34
Yeah, just found this http://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server . – Pranay Deep Dec 22 '16 at 09:39

Select Comma Separate values in SQL server 2012

1 Answers1