在sql表或c#数据表中连接一列的字符串值

本文关键字：一列 字符串 表或 sql 数据表 连接 | 更新日期: 2023-09-27 17:51:15

我在SQL/c#中有一个表/数据表，数据如下:

  Description   Name
    ABC           AB
    ABCD          AB
    ABCD          CD
    ABCDF         AB
    ABCD          BC
    ABCDF         BC

我试图得到如下输出(名称应该连接为描述):

  Description   Name
    ABC           AB
    ABCD          AB, BC and CD
    ABCDF         AB and BC

我在输出中按字母顺序写了名字，但这不是必需的。例如，第二行的Name可以是AB、CD和BC，也可以是BC、CD和AB。我只需要一个字符串中的所有名称用于特定的描述。

我如何在SQL或c#中获得所需的输出?

在sql表或c#数据表中连接一列的字符串值

我得到了c#和SQL的解决方案，是否有其他方法可以获得相同的输出?如果性能问题，我应该使用哪种方法(sql或c#) ?

c#: 

static void Main(string[] args)
        {
            DataTable dt = new DataTable("MyTable");
            dt.Columns.Add(new DataColumn("Name"));
            dt.Columns.Add(new DataColumn("Description"));
            DataRow dr = dt.NewRow();
            dr["Name"] = "AB";
            dr["Description"] = "ABC";
            dt.Rows.Add(dr);
            DataRow dr1 = dt.NewRow();
            dr1["Name"] = "AB";
            dr1["Description"] = "ABCD";
            dt.Rows.Add(dr1);
            DataRow dr2 = dt.NewRow();
            dr2["Name"] = "CD";
            dr2["Description"] = "ABCD";
            dt.Rows.Add(dr2);
            DataRow dr3 = dt.NewRow();
            dr3["Name"] = "AB";
            dr3["Description"] = "ABCDF";
            dt.Rows.Add(dr3);
            DataRow dr4 = dt.NewRow();
            dr4["Name"] = "BC";
            dr4["Description"] = "ABCD";
            dt.Rows.Add(dr4);
            DataRow dr5 = dt.NewRow();
            dr5["Name"] = "BC";
            dr5["Description"] = "ABCDF";
            dt.Rows.Add(dr5);
            StringBuilder sb = new StringBuilder();
            var grouped = from table in dt.AsEnumerable()
                            group table by new { DescriptionCol = table["Description"] } into groupby
                            select new
                          {
                              Value = groupby.Key,
                              ColumnValues = groupby
                          };
            foreach (var key in grouped)
            {
                Console.WriteLine(key.Value.DescriptionCol);
                Console.WriteLine("---------------------------");
                int i = 1;
                foreach (var columnValue in key.ColumnValues)
                {
                    string comma = " , ";
                    string and = " and ";
                    if (i > 1 && i < key.ColumnValues.Count())
                    {
                        sb.Append(comma);
                    }
                    else if (i > 1 && i == key.ColumnValues.Count())
                    {
                        sb.Append(and);
                    }
                    sb.Append(columnValue["Name"].ToString());
                    i++;
                }
                Console.WriteLine(sb.ToString());
                sb.Clear();
                Console.WriteLine();
            }
            Console.ReadLine();
        }
SQL: 

create table myTable
(
  Description  nvarchar(100),
  Name nvarchar(100)
)
insert into myTable values
('ABC','AB'),
('ABCD','AB'),
('ABCD','CD'),
('ABCD','BC'),
('ABCDF','BC'),
('ABCDF','AB')

create table myTable1
(
  Description  nvarchar(100),
  Name nvarchar(100)
)
insert into mytable1 (Description, Name)
SELECT Description, Name = STUFF((SELECT ', ' + Name 
    FROM myTable AS t2 WHERE t2.Description = t.Description
     ORDER BY Description
     FOR XML PATH('')), 1, 1, '')
FROM myTable AS t
GROUP BY Description
ORDER BY Description;
--select * from mytable1
select 
    Description, 
    ISNULL(
       REVERSE(STUFF(REVERSE(Name), CHARINDEX(',', REVERSE(Name), 0),1,'dna ')), 
    Name) Name
From mytable1
select Description, SUBSTRING(Name, 0, (len(Name) - charindex(',', reverse(Name)))) +
Replace(SUBSTRING(Name, (len(Name) - charindex(',', reverse(Name))), len(Name)), 
', ', ' and ') from mytable1

如果要将多行连接到单个文本字符串中，有不同的方法。SQL的答案取决于您的数据库系统。在Microsoft SQL Server上可以使用FOR XML PATH方法。请参阅此处的讨论将多行连接到单个文本字符串中?

CREATE TABLE tb(aaa INT,bbb INT)
Go 
INSERT INTO tb
SELECT 1,2 UNION ALL
SELECT 1,3 UNION ALL
SELECT 1,4 UNION ALL
SELECT 2,2 UNION ALL
SELECT 2,5 

select 
aaa, 
[values]=stuff(replace(replace((select [bbb] 
                                from tb 
                                where aaa=t.aaa for xml AUTO),
                                '"/><tb bbb="',','),
                        '"/>',''),1,9,'')
from tb t
group by aaa
相关文章：