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

Question

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!

Answer 1

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)