将多个列值作为逗号分隔值放入单个列中

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

我有一个表CommentsTable与列,如CommentA,CommentB,CommentC,CommentD,CommentE。

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

所有注释列都是VARCHAR(200),默认情况下所有列都是NULL。

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

但是发生了不想要的逗号,所以添加了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.

但是在这里,逗号发生在某些情况的最后位置(如果CommentD,CommetE为NULL。

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

有没有办法实现解决所有案件。

Sample SQL Fiddle

示例SQL小提琴

3 个解决方案

#1

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

你可以像这样的ISNULL(','+ CommentA,'')使用ISNULL并像这样编写你的查询。

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.

在SQL Fiddle中查看结果。

#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

#3

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.

上面的答案是正确的,并且对接受的答案没有挑战,但是如果某些列有空字符串而不是null,那么下面可能有帮助。如果错误的话,请不要犹豫,采取更好的方法并纠正我。

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

#1