TSQL Comma Separation

问题:

I'm writing an export function, where I need to export contacts to Excel, and I've run into a technical snag - or perhaps a gap in my SQL skills is closer to the truth. ;)

Here's the scenario: I've got a bunch of contacts in a database. Each contact can have many different roles, for example a contact can be both C# Developer and DBA, or DBA and IT-manager. These are split into three tables, like so:

-------------------   -------------------   -------------------
*     Contact     *   *   ContactRole   *   *      Role       *
-------------------   -------------------   -------------------
* ID              *   * ContactID       *   * ID              *
* Name            *   * RoleID          *   * Name            *
* Address         *   -------------------   -------------------
-------------------   

Not too hard to follow. There's a set of contacts, and a set of roles. These are joined by the ContactRole table on the respective IDs.

When I export the contacts, I need to have a column in the export with all the roles comma separated, like C# Developer, DBA or DBA, IT-manager. The export will be done from ASP.NET/C# codebehind, so I figured I could do this in code should it come to that, but I've got a feeling it's possible to do in the SQL.

The data comes from SQL Server 2005.

回答1:

Try this

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 

update:

Above code covers functionality for a single contact. You can create a scalar function with parameter @ContactID and call the function from a

Select Name, dbo.GetContactRoles(ID) From Contact

回答2:

Just because you use SQL Server 2005 (and if you are lucky and have all XML settings properly set), here is your simple SQL query (pure SQL and no functions):

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

To test if it works for you, just execute the whole snippet below:

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

and you should get the following result:

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

回答3:

You can use a CLR user-defined aggregate to get such results. The user-defined aggregate can be invoked like the user-defined ones (e.g. SUM or MAX) and it doesn't use a cursor.

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
}

In this posts you'll find the code as well as my solution of debugging problems I encountered.

I used this aggregate in production environment and it performed really well.

回答4:

You can do it in a single query, though I don't know if the performance is good or bad.

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>]

That CASE statement inside is just to remove the last comma from the list--you have to ORDER BY something for the inner query and then reverse that ORDER BY in the CASE statement.

回答5:

SQL Query:

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)

Next proceed with application logic

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

回答6:

EDIT: Rewritten from table to scalar function based on devio's idea so if you like this post vote for his answer.

If CLR integration is not an option, you can accomplish this with a scalar function:

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

You can then call this function to calculate the comma-separated list for each contact:

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

回答7:

You can write a function which outputs the roles as comma separated string when you pass it contact id. Then call this function in your select statement :)

For example if you want to fetch products that are ordered by a customer in a particular order you can use this code:

    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

now you can use the function as follows:

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