用“and"替换最后一项的逗号分隔符在 Sql STUFF 命令中 [英] Replace the last item's comma separator with "and" in Sql STUFF Command

问题描述

如果我查询的输出是英国、美国、印度.

If the output of my query is England, America, India.

是否可以像英国、美国和印度

Is it possible to display the query result like England, America and India

这是我的查询:

Select stuff( ( Select Distinct ',' + Country as[text()] 
from tbl_Country 
where OrderNo in (Select Max(OrderNo) from Tbl_Literature where ID='AB123' 
                  Group By OrderNo) for XML path('') ),1,1,'')as Result 
from dbo.Tbl_Master as TP 
where 
  TP.OrderNo IN (select MAX(order No) from dbo.Tbl_Master where TP.ID = 'AB123') 

推荐答案

假设您指的是为弥补 SqlServer 缺乏 GROUP_CONCAT，你可以用这个巧妙的破解在这里:

Assuming you are referring to the string aggregation hack done to compensate for SqlServer's lack of GROUP_CONCAT, you can use this ingenious hack here:

WITH myStuffedCte AS
(SELECT 
    STUFF((
          SELECT ',' + Name
          FROM Country
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS SomeCol
)
SELECT ISNULL(reverse(replace(STUFF(reverse(SomeCol),
                      charindex(',',reverse(SomeCol)),0,'#'),'#,',' dna ')),
              SomeCol)
FROM myStuffedCte;

这里的SqlFiddle

使用 GROUPING 条件更新 Stuff 示例

Updated example of Stuff with a GROUPING criteria

为了以 GROUP BY 方式使用 STUFF，您可以根据带有 WHERE子句.

In order to use STUFF in a GROUP BY fashion, you can manually filter the inner STUFF subquery based on an outer query's rows with a WHERE clause.

WITH myStuffedCte AS
(
    SELECT p.FirstName, p.LastName,
        STUFF((
              SELECT ',' + c.Name
              FROM Country c
              INNER JOIN PersonCountryVisits pcv
              ON pcv.CountryId = c.CountryId
              WHERE pcv.PersonId = p.PersonID -- Manual Filter step
              FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS SomeCol
    FROM Person p
)
SELECT FirstName, LastName,
   ISNULL(reverse(replace(STUFF(reverse(SomeCol),
          charindex(',',reverse(SomeCol)),0,'#'),'#,',' dna ')), 
          SomeCol) AS CountriesVisited
FROM myStuffedCte;

此处更新了 Sql Fiddle 示例

SqlClr 替代 Stuff，如果你可以在你的 SqlServer 上使用 SqlClr:
如果您有权访问启用了 Sql-Clr 的 SqlServer，您可以放弃这两个技巧，而是将 STUFF/FOR XML 替换为 GROUP_CONCAT 模拟 SqlUserDefinedAggregate 喜欢这个，同时用SqlFunction UDF，像这样:

SqlClr Alternative to Stuff, IFF you can use SqlClr on your SqlServer:
If you have access to a Sql-Clr enabled SqlServer, you can ditch both of these hacks and instead, replace STUFF / FOR XML with a GROUP_CONCAT simulation of SqlUserDefinedAggregate like this one, and at the same time do the last comma replacement with a SqlFunction UDF, like this:

[SqlFunction]
public static SqlString ReplaceLast(SqlString source, SqlString find, SqlString replace, 
                                    int countFromRight)
{
    var splits = source.Value
        .Split(new[] {find.Value}, StringSplitOptions.None);

    var replacePoint = splits.Length - countFromRight;

    return (replacePoint > 0)
        ? new SqlString(
            string.Join(replace.Value,
                new[] { string.Join(find.Value, splits.Take(replacePoint)) }
                .Union(splits.Skip(replacePoint))))
        : source;
}

在这种情况下，示例查询变得更加可口:

In which case the sample query becomes the entirely more palatable:

SELECT FirstName, LastName, 
    dbo.ReplaceLast(dbo.GROUP_CONCAT(c.Name, ','), ',', ' and ', 1)
FROM  Person p 
    INNER JOIN PersonCountryVisits pcv
    ON pcv.PersonId = p.PersonID
    INNER JOIN Country c
    ON pcv.CountryId = c.CountryId
GROUP BY FirstName, LastName;

我已经发布了一个 Gist on GitHub，它提供了所有重要的部分来创建GROUP_CONCAT 和 ReplaceLast SqlClr 函数.(SqlFiddle 不可用 - 没有 SQLClr ...)

I've put up a Gist on GitHub which provides all the salient bits to create the GROUP_CONCAT and ReplaceLast SqlClr functions. (SqlFiddle not available - No SQLClr ...)

这篇关于用“and"替换最后一项的逗号分隔符在 Sql STUFF 命令中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！