Multiple column values ​​in one column as comma separated value

Question

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

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

The data looks like this:

 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 null values ​​and the remaining values ​​are the same as comma .

So, the expected result, for example:

 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 a simple request:

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

But there are unwanted comma , so IIF added

 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, comma happened in the last position for some cases (If CommentD, CommetE - NULL .

Is there a way to reach a solution for all cases.

Sample SQL Fiddle

Answer 1

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

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

See the result of SQL Fiddle .

Answer 2

 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 

Answer 3

The above answers are correct and do not require an answer to the accepted answer, but if some columns have an empty row instead of zero, then below can help. Please feel free to better approach and correct me if it is 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