Multiple column values into a single column as comma separated value

Question

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

Answer 1

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.

Answer 2

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