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

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

Select Name, dbo.GetContactRoles(ID) From Contact

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
}

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,

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;

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

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

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'