逗号分隔

因为您使用的是SQL Server 2005(如果幸运的话,您已经正确设置了所有XML设置),下面是您的简单SQL查询(纯SQL,没有函数):

SELECT  c.ID, c.Name, c.Address, 
    (   SELECT      r.Name + ','
        FROM        "ContactRole" cr
        INNER JOIN  "Role" r
                ON  cr.RoleID = r.ID
        WHERE       cr.ContactID = c.ID
        ORDER BY    r.ID --r.Name
        FOR XML PATH('')
    ) AS "Roles"
FROM    "Contact" c

要测试它是否适合您,只需执行以下整个代码段:

WITH "Contact" (ID, Name, Address) AS (
                SELECT 1, 'p1-no role', NULL
    UNION ALL   SELECT 2, 'p2-one role', NULL
    UNION ALL   SELECT 3, 'p3-two roles', NULL
)
, "Role" (ID, Name)AS (
                SELECT 1, 'teacher'
    UNION ALL   SELECT 2, 'student'
)
, "ContactRole" (ContactID, RoleID) AS (
                SELECT 2, 1
    UNION ALL   SELECT 3, 1
    UNION ALL   SELECT 3, 2
)

SELECT  c.ID, c.Name, c.Address, 
    (   SELECT      r.Name + ','
        FROM        "ContactRole" cr
        INNER JOIN  "Role" r
                ON  cr.RoleID = r.ID
        WHERE       cr.ContactID = c.ID
        ORDER BY    r.ID --r.Name
        FOR XML PATH('')
    ) AS "Roles"
FROM    "Contact" c

您应该得到以下结果:

ID          Name         Address     Roles
----------- ------------ ----------- ------------------
1           p1-no role   NULL        NULL
2           p2-one role  NULL        teacher,
3           p3-two roles NULL        teacher,student,

CLR用户定义的聚合 为了得到这样的结果。用户定义的聚合可以像用户定义的聚合一样调用(例如SUM或MAX),并且它不使用游标。

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
{
    #region Private fields
    private string separator;
    private StringBuilder intermediateResult;
    #endregion

    #region IBinarySerialize members
    public void Read(BinaryReader r)
    {
        this.intermediateResult = new StringBuilder(r.ReadString());
    }

    public void Write(BinaryWriter w)
    {
        w.Write(this.intermediateResult.ToString());
    }
    #endregion

    #region Aggregation contract methods
    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();
    }
    #endregion
}

在里面 this posts

我用了这个骨料 生产环境

试试这个

declare @Roles nvarchar(max)

select @Roles = case when @Roles is null then '' else @Roles + ', ' end + Role.Name
from  Role
inner join ContactRole on Role.ID = ContactRole.RoleID
where ContactRole.ContactID = @ContactID

select @Roles 

更新:

以上代码涵盖了单个联系人的功能。您可以使用参数@ContactID创建标量函数,并从

Select Name, dbo.GetContactRoles(ID) From Contact

SELECT [<group field 1>], [<group field 2>], [etc...], (
    SELECT CAST([<field to list>] AS VARCHAR(MAX)) + 
        CASE WHEN (ROW_NUMBER() OVER (ORDER BY [<inner order-by REVERSED>]) = 1)
            THEN '' ELSE ',' END
        AS [text()]
    FROM [<inner table>]
    WHERE [<inner table join field>] = [<outer table join field>]
    AND [<inner conditions>]
    ORDER BY [<inner order-by>]
    FOR XML PATH('')) AS [<alias>]
FROM [<outer table]
WHERE [<outer conditions>]

颠倒

SQL查询:

SELECT Contact.Name as cName, Role.Name as rName FROM Contact 
JOIN ContactRole ON (Contact.ID==ContactRole.ContactID)
JOIN Role ON  ON (Role.ID==ContactRole.RoleID)

接下来继续应用程序逻辑

forloop:
    array[ cName ] .= rName.', ';
endforloop;

编辑:根据从表重写为标量函数 devio's idea 所以,如果你喜欢这篇文章,请投他的票。

create function dbo.getRole(
    @ContactId int)
returns varchar(8000)
as
begin
declare @Roles varchar(8000)

select 
    @Roles = case when @Roles is null then '' else @Roles + ', ' end + Role.Name
from Role
inner join ContactRole on Role.ID = ContactRole.RoleID
where ContactRole.ContactID = @ContactID

return @Roles

然后可以调用此函数来计算每个联系人的逗号分隔列表:

SELECT c.id, c.name, dbo.getRole(ID) as Roles
FROM Contact

您可以编写一个函数,在传递联系人id时将角色作为逗号分隔的字符串输出。 然后在select语句中调用此函数:)

例如,如果要获取客户以特定顺序订购的产品,可以使用以下代码:

    create function FetchProducts(@orderid int) returns varchar(1000)
    as
    begin
    declare prods cursor for select ProductName from products where 
            productid in (select ProductId from [Order Details] 
              Where OrderId = @orderid)

    open prods

    declare @products  varchar(1000)
    declare @cp varchar(500)
    Select @products = ''
    fetch prods into @cp

    while @@fetch_status = 0
    begin
        SET @products = @products + ',' + @cp
        fetch prods into @cp
    end

    close prods
    deallocate prods

    return substring(@products, 2, len(@products)-1)
    end

现在,您可以使用以下功能:

    select orderid, orderdate, dbo.FetchProducts(orderid) 
from orders where customerid = 'BERGS'