在特定条件下改变数据表的结构

本文关键字：数据表 结构 改变 条件下 | 更新日期: 2023-09-27 18:02:47

我有一个像这样的数据表prdData 

productid        text                    linenumber    typenumber
100              this is the                 0             2
100              description of a1           2             2 
200              this is the name of a2      0             0
100              this is the name of a1      0             0
200              shortdescription of a2      0             1

在该表中存储产品数据。存储的数据为产品编号、产品名称、短描述、长描述。如果typenumber是0它的名称，如果1它的简短描述，如果2它的长描述。如果值很长，这些数据可能在不同的行中出现。每一行都可以用linenumber来标识，第一行是0，接下来是2，以此类推。我想把这个数据表转换成另一个数据表，像这样

productId   name                    shortdescription             longdescription
100         this is the name of a1                               this is the description of a1 
200         this is the name of a2   shortdescription of a2

谁能告诉我怎么才能做到这一点?

在特定条件下改变数据表的结构

您可以按产品对行进行分组，然后按产品的类型对行进行分组:

var query = from r in table.AsEnumerable()
            group r by r.Field<int>("productid") into g
            let types = g.GroupBy(r => r.Field<int>("typenumber")) 
            select new {
               productId = g.Key,
               name = GetText(types.FirstOrDefault(t => t.Key == 0)),
               shortdescription = GetText(types.FirstOrDefault(t => t.Key == 2)),
               longdescription = GetText(types.FirstOrDefault(t => t.Key == 1))
         };

其中helper方法只是按行号排序类型行并返回连接的文本

private static string GetText(IEnumerable<DataRow> rows)
{
    if (rows == null)
        return null;
    var query = from r in rows
                orderby r.Field<int>("linenumber")
                select r.Field<string>("text");
    return String.Join(" ", query);                   
}
输出:

[
  {
    productId: 100,
    name: "this is the name of a1",
    shortdescription: "this is the description of a1",
    longdescription: null
  },
  {
    productId: 200,
    name: "this is the name of a2",
    shortdescription: null,
    longdescription: "shortdescription of a2"
  }
]

您可以手动或使用CopyToDataTable方法构建新的数据表。您还可以使用Linq to Xml构建Xml:

var xdoc = new XDocument(new XElement("products",
              from p in query
              select new XElement("product",
                  new XAttribute("productId", p.productId),
                  new XElement("name", p.name),
                  new XElement("shortDescription", p.shortdescription),
                  new XElement("longDescription", p.longdescription))));
输出:

<products>
  <product productId="100">
    <name>this is the name of a1</name>
    <shortDescription>this is the description of a1</shortDescription>
    <longDescription />
  </product>
  <product productId="200">
    <name>this is the name of a2</name>
    <shortDescription />
    <longDescription>shortdescription of a2</longDescription>
  </product>
</products>

或者(可能)更简单的解决方案——不使用匿名类型，而是创建可以轻松序列化为xml的自定义类

相关文章：