将行值合并为CSV（适用于SQL Server的a.k.a GROUP_CONCAT）

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!

任何帮助表示赞赏！

3 个解决方案

#1

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

对于每对EntityID，AttributeID使用XML路径技巧生成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)

#2

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: http://data.stackexchange.com/*/q/115141/

尝试下面的代码（我已经包含了所有测试SQL，因此您不必练习实时数据）。您可以在此处查看工作示例：http：//data.stackexchange.com/*/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

#3

I have done some tests with 3 different methods:

我用3种不同的方法做了一些测试：

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

Hope it helps.

希望能帮助到你。

#1