将列条目旋转到单行中逗号分隔的列表中，以区分不同的相邻列? [英] Pivot column entries into a comma separated list in a single row for distinct adjacent column?

问题描述

SELECT Parent, Child FROM Daycare_Contacts
GROUP BY Parent, Child;

返回结果集

Parent     Child
Bob        Brett
Bob        Cindy
Bob        John
Alice      Pierre
Alice      John

我希望以逗号分隔的列表形式返回子项，并以不同的父项作为相邻列.像这样:

I would like it to return children as a comma separated list with the distinct parent as the adjacent column. Like so:

Parent     Child
Bob        Brett, Cindy, John
Alice      Pierre, John

如果可以在LINQ中完成，我也愿意接受.

If it can be done in LINQ, I'd be open to that as well.

推荐答案

1)您可以从T-SQL返回逗号分隔的数据.

1) You can return a comma separated data from T-SQL.

为此，您需要使用

For this goal you need to use FOR XML command.

您可以查看详细信息请检查下一个T-SQL脚本:

Please check next T-SQL script:

DECLARE @Daycare_Contacts TABLE
(
    Parent VARCHAR(100),
    Child  VARCHAR(100)
)

INSERT INTO @Daycare_Contacts
VALUES ('Bob', 'Brett'), ('Bob', 'Cindy'), ('Bob', 'John'), ('Alice', 'Pierre'), ('Alice', 'John')

SELECT 
    Parent,
    STUFF((SELECT ', ' + Child AS 'text()' FROM @Daycare_Contacts t WHERE t.PArent = dc.Parent FOR XML PATH('')), 1, 2, '') AS Childs
FROM @Daycare_Contacts dc
GROUP BY Parent;

结果:

Parent    Childs
--------------------------
Alice     Pierre, John
Bob       Brett, Cindy, John

2)您也可以使用LINQ.

2) Also you can use LINQ.

您需要使用示例和详细信息在此处.

我的小提琴: .NET小提琴

C#代码示例:

using System;
using System.Linq;
using System.Collections;
using System.Collections.Generic;

public class Program
{
    public class SomeClass
    {
        public string Parent { get; set; }
        public string Child { get; set; }
    }


    public static void Main()
    {
        var datas = new List<SomeClass>
        {
            new SomeClass{ Parent = "Bob", Child = "Brett" },
            new SomeClass{ Parent = "Bob", Child = "Cindy" },
            new SomeClass{ Parent = "Bob", Child = "John" },
            new SomeClass{ Parent = "Alice", Child = "Pierre" },
            new SomeClass{ Parent = "Alice", Child = "John" }
        };

        var groups = datas.GroupBy(n => n.Parent)
            .Select(n => new
            {
                Parent = n.Key,
                Childs = string.Join(",", n.Select(i => i.Child))
            })
            .ToList();

        var result = string.Join("\n", groups);

        Console.WriteLine(result);
    }
}

这篇关于将列条目旋转到单行中逗号分隔的列表中，以区分不同的相邻列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！