时间戳

首页 > 解决方案 > DatagridView 中的动态数据透视表

sql-server - DatagridView 中的动态数据透视表

问题描述

我在 VB.net 中有一个项目，它将 SQL 服务器作为数据库。表名是Variation，它有列

JobNo
VariationNo
VariationRef
Amount
我不知道一个特定的 JobNo 会有多少变化。我需要转换这个

  JobNo   |  VariationNo  |  VariationRef  |  Amount
  1000    |      1        |      ABC       |  10 
  1000    |      2        |      ABD       |  15
  1100    |      1        |      ABE       |  50 
  1100    |      2        |      ABF       |  55 
  1100    |      3        |      ABG       |  60

进入这个

  JobNo   |  V-1  |   V-2  |   V-3  etc.. to the last number
  1000    |   10  |    15  |    0       
  1100    |   50  |    55  |    60

并显示它datagridview 那么我的字符串（SQLString）应该是什么？
我的代码如下

Dim con As New SqlConnection("Server=SREE-PC\SQLEXPRESS04;Database=project;Integrated security=true")  
Dim SQLString As String =???  
Dim cmd As New SqlCommand(SQLString, con)  
    Dim adapter As New SqlDataAdapter(cmd)  
    Dim table As New DataTable  
    adapter.Fill(table)  
    DataGridView1.DataSource = table

标签： sql-servervb.net

解决方案

我用下面的代码得到了我想要的结果。Andrew Morton 先生在字符串连接部分帮助了我。但我觉得我的解决方案有点冗长，尤其是在开始时。有什么建议吗？

        Dim SQLString1 As String = "Select STRING_AGG([VariationNo],',')Intcolumn from (select distinct variationNo from variation)tmp"
        Dim cmd1 As New SqlCommand(SQLString1, con)
        Dim adapter1 As New SqlDataAdapter(cmd1)
        Dim table1 As New DataTable
        adapter1.Fill(table1)
        Dim tmplst As String = table1.Rows(0).Item("IntColumn").ToString
        Dim clmName As String = "[" & tmplst.Replace(",", "],[") & "]"
        Dim addvar As String = " as [Variation-" & tmplst.Replace(",", "]/ as [Variation-") & "]"
        Dim tmpclmname As String = "IsNull(" & clmName.Replace(",", ",0)/IsNull(") & ",0)"
        Dim joined = tmpclmname.Split({"/"c}).Zip(addvar.Split({"/"c}), Function(x, y) x & y).ToList()
        Dim tmpclmname1 = String.Join(", ", joined)
        Dim clmName1 As String = tmpclmname1.Replace("/", ",")
 Dim SQLString As String = "Select JobNo," & clmName1 & "
                                   from (Select JobNo,VariationNo,Amount from variation) as pivotdata     
                                   pivot 
                                   (
                                        Max(Amount)
                                        For VariationNo
                                        In (" & clmName & ")                                                         
                                   )AS Pivoting"
Dim cmd As New SqlCommand(SQLString, con)
        Dim adapter As New SqlDataAdapter(cmd)
        Dim table As New DataTable
        adapter.Fill(table)
        DataGridView1.DataSource = table

推荐阅读