CODEWITHSUNDEEP
sqlsql-server
Arulkumar
Arulkumar

Reputation: 13237

Multiple column values into a single column as comma separated value

I have a table CommentsTable with columns like, CommentA, CommentB, CommentC, CommentD, CommentE.

All comments columns are VARCHAR (200), by default all columns are NULL also.

The data looks like:

CommentId   CommentA    CommentB    CommentC    CommentD    CommentE
---------------------------------------------------------------------
12345       NULL        C 001       C 002       NULL        C 003
45678       C 005       NULL        NULL        C 007       NULL
67890       C 010       NULL        C 011       C 012       NULL
36912       C 021       C 023       C 024       C 025       C 026

I need to avoid the null values and the remaining values are concatenate with comma.

So, the expected output like:

CommentId    CommetDetails
-------------------------------
12345        C 001, C 002, C 003
45678        C 005, C 007
67890        C 010, C 011, C 012
36912        C 021, C 023, C 024, C 025, C 026

I tried with simple query:

SELECT CommentId, ISNULL(CommentA, '') + ', ' + ISNULL(CommentB, '') + ', ' + 
       ISNULL(CommentC, '') + ', ' + ISNULL(CommentD, '') + ', ' +
       ISNULL(CommentE, '') [CommentDetails]
FROM CommentsTable
WHERE ...... --Some conditions

But the unwanted comma are occurred, So added IIF

SELECT CommentId, 
       IIF(ISNULL(CommentA, '') <> '', (CommentA + ', '), '') +
       IIF(ISNULL(CommentB, '') <> '', (CommentB + ', '), '') +
       IIF(ISNULL(CommentC, '') <> '', (CommentC + ', '), '') +
       IIF(ISNULL(CommentD, '') <> '', (CommentD + ', '), '') +
       ISNULL(CommentE, '') [CommentDetails]
FROM CommentsTable
WHERE ...... --Some conditions

But here also, the comma occurred in the last position for some cases (If CommentD, CommetE are NULL.

Is there any way to achieve to solve for all the cases.

Sample SQL Fiddle

Upvotes: 8

Views: 34189

Answers (3)

Sweetie
Sweetie

Reputation: 1368

The above answers are correct and no challenge to the accepted answer but in case some columns have empty string instead of null then below might help. Please don't hesitate for a better approach and correct me if it's wrong.

SELECT CommentId, 
STUFF(
ISNULL(',' + CASE WHEN CommentA= '' THEN NULL ELSE CommentA END, '') + 
ISNULL(',' + CASE WHEN CommentB= '' THEN NULL ELSE CommentB END, '') + 
ISNULL(',' + CASE WHEN CommentC= '' THEN NULL ELSE CommentC END, '') + 
ISNULL(',' + CASE WHEN CommentD= '' THEN NULL ELSE CommentD END, '') +
ISNULL(',' + CASE WHEN CommentE= '' THEN NULL ELSE CommentE END, ''),1,1,'') as [CommentDetails]
FROM CommentsTable

Upvotes: 2

Hell Boy
Hell Boy

Reputation: 981

create table #test
(
CommentId int,
CommentA nvarchar(200),
CommentB nvarchar(200),
CommentC nvarchar(200),
CommentD nvarchar(200),
CommentE nvarchar(200)

)
insert into #test values(12345,NULL,'C 001','C 002',NULL,'C 003')
insert into #test values(45678,'C 005',NULL,NULL,'C 007',NULL)
insert into #test values(67890,'C 010',NULL,'C 011','C 012',NULL)
insert into #test values(36912,'C 021','C 023','C 024','C 025','C 026')

Use This Code:

select CommentId,STUFF(ISNULL(','+CommentA,'')+
                       ISNULL(','+CommentB,'')+
                       ISNULL(','+CommentC,'')+
                       ISNULL(','+CommentD,'')+
                        ISNULL(','+CommentE,''),1,1,'') As Comment 
 from #test
 order by CommentId

Upvotes: 0

ughai
ughai

Reputation: 9880

You can use ISNULL like this ISNULL(',' + CommentA, '') and write your query like this.

SELECT CommentId, 
STUFF(
ISNULL(',' + CommentA, '') + 
ISNULL(',' + CommentB, '') + 
ISNULL(',' + CommentC, '') + 
ISNULL(',' + CommentD, '') +
ISNULL(',' + CommentE, ''),1,1,'') as [CommentDetails]
FROM CommentsTable
WHERE ...... //Some conditions

See result in SQL Fiddle.

Upvotes: 15

Related Questions