用逗号或" AND "连接数据库表列的所有值

I have the following requirement for a SQL Server Reporting Services(SSRS) report: display all the values from a column in a database table in one string, concatenated as follows:

对于SQL Server Reporting Services(SSRS)报表，我有以下要求:将数据库表中列中的所有值以一个字符串显示，并连接如下:

if there is just one value, display it

如果只有一个值，显示它
if there are two values, concatenate them with a comma (",")

如果有两个值，用逗号("，")连接它们
if there are more than two values, concatenate all except the last two with a comma (",") and the last two with ", AND"

如果有两个以上的值，用逗号("，")连接所有的值，最后两个用"和"
values should be distinct, i.e. no two repeating values are allowed

值应该是不同的，即不允许有两个重复值

To illustrate, if the values of the column in question, let's call it Column1, are:

为了举例说明，如果所讨论的列的值，我们称它为Column1，是:

Column1

Apple

苹果

Potato

土豆

Potato

土豆

Pear

梨

Grapes

葡萄

The resulting string should be:

产生的字符串应该是:

Apple, Potato, Pear, and Grapes

苹果，土豆，梨和葡萄

So my question is, how can i do that in a SQL statement? I can accomplish #1, #2, and #4 with the following SQL, but #3 escapes me:

我的问题是，如何在SQL语句中这样做呢?我可以用下面的SQL来完成#1，#2，#4，但是#3逃过我:

SELECT Stuff(
  (SELECT DISTINCT
        N', ' + Table1.Column1
   FROM
        Table2
                INNER JOIN Table ON Table1.Id = Table2.Table2ID_FK
   WHERE
    dbo.Table2.SomeId = 100
    FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'');

3 个解决方案

#1

Try something like this

这样的尝试

SELECT CASE
         WHEN Len(intr) - Len(Replace(intr, ',', '')) > 1 THEN Stuff(intr, ( Len(intr) - Charindex(',', Reverse(intr)) ) + 2, 0, ' and ')
         ELSE intr
       END
FROM   (SELECT Stuff((SELECT DISTINCT N', ' + Table1.Column1
                      FROM   Table2
                             INNER JOIN Table
                                     ON Table1.Id = Table2.Table2ID_FK
                      WHERE  dbo.Table2.SomeId = 100
                      FOR XML PATH(''), TYPE) .value('text()[1]', 'nvarchar(max)'), 1, 2, N'') AS intr)a

#2

Leave your code (which is working fine) alone. Outside of that code, fix the issue. Here's how...

让您的代码(运行良好)保持独立。在代码之外，修复问题。这就是……

Temporarily, I'll use literals for clarity. Consider that:

暂时，我将使用文字来表达清楚。考虑到:

    SELECT RIGHT('Apple, Potato, Pear, Grapes' , CHARINDEX (' ,' ,REVERSE('Apple, Potato, Pear, Grapes'))+1)

...yields: ", Grapes"

…收益率:“葡萄”

Good so far? Just replace that with "and Grapes" by doing the following...

到目前为止,好吗?用“和葡萄”来替换它，做下面的事情……

    SELECT REPLACE('Apple, Potato, Pear, Grapes', RIGHT('Apple, Potato, Pear, Grapes' , CHARINDEX (' ,' ,REVERSE('Apple, Potato, Pear, Grapes'))+1), REPLACE(RIGHT('Apple, Potato, Pear, Grapes' , CHARINDEX (' ,' ,REVERSE('Apple, Potato, Pear, Grapes'))+1),',',' and'))

So, to make that all dynamic, just replace the literal 'Apple, Potato, Pear, Grapes' with the results of your original query (that you should assign to a variable).

因此，要使这一切都是动态的，只需将文字“Apple, Potato, Pear, Grapes”替换为原始查询的结果(您应该为变量赋值)。

The final code looks like:

最后的代码如下:

    DECLARE @OrigValue AS NVARCHAR(MAX)

    SELECT @OrigValue = 
      Stuff(
      (SELECT DISTINCT
            N', ' + Table1.Column1
       FROM
            Table2
                    INNER JOIN Table ON Table1.Id = Table2.Table2ID_FK
       WHERE
        dbo.Table2.SomeId = 100
        FOR XML PATH(''),TYPE)
      .value('text()[1]','nvarchar(max)'),1,2,N'');

    SELECT REPLACE(@OrigValue, RIGHT(@OrigValue , CHARINDEX (' ,',REVERSE(@OrigValue))+1), REPLACE(RIGHT(@OrigValue, CHARINDEX (' ,',REVERSE(@OrigValue))+1),',',' and'))

#3

Using with (common table expression) we can reference target result set easily with subqueries to find the last value and the count of values in the list.

使用with (common table expression)，我们可以使用子查询很容易地引用目标结果集，以查找列表中的最后一个值和值的计数。

Using a case expression to add 'and ' when col = the last value and a subquery to check the count(*) of the values being returned:

使用case表达式添加'and ' when col =最后一个值，使用子查询检查返回值的计数(*):

rextester: http://rextester.com/QDTJ44637

rextester:http://rextester.com/QDTJ44637

with t as (
  select col 
  from (values ('Apple'),('Potato'),('Potato'),('Pear'),('Grapes')) t (col)
)

select stuff((
  select 
        N', ' 
      + case when col = (select top 1 col from t order by col desc)
               and (select count(*) from t)>2
             then 'and ' 
             else '' 
             end 
      + col
   from t
   group by col
   order by col for xml path(''),type)
  .value('text()[1]','nvarchar(max)'),1,2,N'');

To adapt this for the query in the question, replace the query inside the common table expression with t as () with

为了适应这个问题的查询，用t()替换普通表表达式中的查询。

SELECT DISTINCT col = Table1.Column1 
FROM Table2 
  INNER JOIN Table1 ON Table1.Id = Table2.Table2ID_FK 
WHERE dbo.Table2.SomeId = 100

#1