SQL结果以逗号分隔

10 浏览
0 Comments

SQL结果以逗号分隔

我在编写存储过程时遇到了困难。以下是情况说明。我有一张表，如下所示：

| 名字 | 分数 |

| A | 10 |

| A | 20 |

| A | 30 |

| B | 20 |

| B | 50 |

我想要通过存储过程从中获取以下结果：

| 名字 | 分数 |

| A | 10,20,30 |

| B | 20,50 |

是否可能通过SQL查询或存储过程获得这样的结果？如何实现？

0

3 答案

0 Comments

“SQL Results Separated by Commas”这个问题的出现的原因是使用FOR XML PATH('')方法时，当需要连接包含保留XML字符的[N][VAR]CHAR值时，结果会出现奇怪的情况。例如，当输入的值为'10', '<20>', '30'时，结果会变成&lt;20&gt;,10,30。这是因为FOR XML PATH('')方法无法正确处理保留XML字符。

解决这个问题的方法是使用.value()函数来获取连接后的字符串。通过使用.value()函数，可以正确地处理包含保留XML字符的字符串，避免出现上述奇怪的结果。

相关参考链接：Rob Farley's的博客文章Handling special characters with FOR XML PATH('')提供了解决这个问题的方法。他的解决方案可以实现对分组ID进行字符串连接，而不用担心字段中可能包含的特殊字符。

下面是一个示例的SQL代码，演示了如何使用.value()函数来解决这个问题：

DECLARE @t TABLE (Name CHAR(1), Score INT) 
INSERT INTO @t VALUES 
('A', 10),
('A', 20), 
('A', 30), 
('B', 20), 
('B', 50)   
SELECT STUFF(
    (SELECT ', ' + CONVERT(VARCHAR(10), Score)
     FROM @t
     WHERE Name = t.Name
     ORDER BY Score
     FOR XML PATH(''),
     TYPE).value('(./text())[1]','varchar(max)'),
   1, 2, '') AS Score     
FROM @t t
GROUP BY Name

通过使用.value()函数，可以正确地连接分组ID的字符串，而不受包含保留XML字符的字段的影响。这样就可以解决“SQL Results Separated by Commas”这个问题。

0
0 Comments

问题：SQL结果用逗号分隔的原因和解决方法

在SQL Server中，有时候我们需要将查询结果以逗号分隔的形式返回。然而，SQL Server本身并没有提供直接的方法来实现这个功能。因此，我们可以使用CLR（Common Language Runtime）集成功能来创建自定义聚合函数来实现这个目的。

解决方法是创建一个CLR用户定义聚合。以下是一个示例代码：

using System;

using System.Data;

using Microsoft.SqlServer.Server;

using System.Data.SqlTypes;

using System.IO;

using System.Text;

[Serializable()]

[SqlUserDefinedAggregate(

Format.UserDefined,

IsInvariantToNulls=true,

IsInvariantToDuplicates=false,

IsInvariantToOrder=false,

MaxByteSize=8000)]

public class Concat : IBinarySerialize

{

// 私有字段

private string separator;

private StringBuilder intermediateResult;

// IBinarySerialize成员

public void Read(BinaryReader r)

{

this.intermediateResult = new StringBuilder(r.ReadString());

}

public void Write(BinaryWriter w)

{

w.Write(this.intermediateResult.ToString());

}

// 聚合合同方法

public void Init()

{

this.separator = ", ";

this.intermediateResult = new StringBuilder();

}

public void Accumulate(SqlString pValue)

{

if (pValue.IsNull)

{

return;

}

if (this.intermediateResult.Length > 0)

{

this.intermediateResult.Append(this.separator);

}

this.intermediateResult.Append(pValue.Value);

}

public void Merge(Concat pOtherAggregate)

{

this.intermediateResult.Append(pOtherAggregate.intermediateResult);

}

public SqlString Terminate()

{

return this.intermediateResult.ToString();

}

}

然后，您可以在查询中像使用其他聚合函数一样使用它：

SELECT Name, dbo.Concat(Score) AS Scores

FROM dbo.Table

GROUP BY Name

通过以上代码，我们可以创建一个名为"Concat"的自定义聚合函数，它将查询结果以逗号分隔的形式返回。您需要编译提供的代码，部署程序集，并使用"CREATE AGGREGATE"语句来定义一个新的用户定义聚合函数。尽管这需要一些工作，但非常值得。详情请参考作者的博客文章《A SQL CLR user-defined aggregate - notes on creating and debugging》。

SQL Server本身不提供直接的方法将查询结果以逗号分隔的形式返回。为了解决这个问题，我们可以使用CLR集成功能创建自定义聚合函数。通过编写CLR代码并将其部署到SQL Server中，我们可以定义一个新的用户定义聚合函数来实现这个功能。这个自定义聚合函数可以像其他聚合函数一样在查询中使用，将查询结果以逗号分隔的形式返回。虽然这需要一些工作，但是它非常有用。

0
0 Comments

在这种情况下，COALESCE是你的朋友。我不是一个COALESCE专家，我只知道它可以工作，所以如果你想深入了解，你可能想查一下。

下面的代码片段将逐行获取结果，在给定要查找的名称时，我会将其转换为SQL Server中的一个函数，然后在上层存储过程中调用该函数，但要注意：如果结果集中有NULL值，将导致你得到不正确的结果。

DECLARE @name varchar(50)

SET @name = 'A'

DECLARE @result varchar(max)

SELECT

@result = COALESCE(@result + ', ','') + CAST(Score AS varchar)

FROM

sotest2

WHERE

name = @name

SELECT @result

顺便说一句，'sotest2'是表名，这只是我在我的数据库中取的名字 🙂

0