Merge row values into a CSV (a.k.a GROUP_CONCAT for SQL Server)

I have a table like:

EntityID   AttributeID  OptionText
5016       20           Paintings
5044       18           Female
5060       48           M
5060       48       开发者_开发百科    F
5060       49           Apple
5060       49           Banana
5060       49           Cat

I want to create a view that will show:

5016    20   Paintings
5044    18   Female
5060    48   M,F
5060    49   Apple, Banana, Cat

Means The attributes values on every entity should be displayed separated by a comma.

The number of options can be varied.

Any help is appreciated!

For each pair of EntityID, AttributeID use the XML path trick to generate the CSV

 SELECT
    M.EntityID, M.AttributeID,
    SUBSTRING(CAST(foo.bar AS varchar(8000)), 2, 7999) AS Options
FROM
    (
    SELECT DISTINCT EntityID, AttributeID
    FROM MyTable
    ) M
    CROSS APPLY
    (
    SELECT
        ',' + OptionText
    FROM
        MyTable M2
    WHERE
        M.EntityID = M2.EntityID AND M.AttributeID= M2.AttributeID
    FOR XML PATH ('')
    ) foo(bar)

Try the code below (I've included all test SQL so you don't have to practice on live data). You can view a working example here: https://data.stackexchange.com/stackoverflow/q/115141/

--Set up test table
CREATE TABLE #Table1 (EntityID INT, AttributeID INT, OptionText VARCHAR(50))

INSERT INTO #Table1
SELECT  5030, 48, 'M'

INSERT INTO #Table1
SELECT  5030, 48, 'F'

--Begin actual working SQL          
SELECT      T1.EntityID,
            T1.AttributeID,
            STUFF(( SELECT    ', ' + T2.OptionText
                    FROM      #Table1 T2
                    WHERE     T2.AttributeID = T1.AttributeID
                    AND       T2.EntityID = T1.EntityID
                    FOR XML PATH('')
                  ), 1, 2, '') [Attributes]
FROM        #Table1 T1
GROUP BY    T1.EntityID, T1.AttributeID

DROP TABLE #Table1

I have done some tests with 3 different methods:

http://blog.feronovak.com/2011/10/multiple-values-in-one-column-aka.html

Hope it helps.