基于MS-Access、SQL、VB.NET实现可更新的唯一值行数据透视表

看来你在处理项目测试数据的展示和编辑需求时，遇到了透视表无法直接编辑的瓶颈。针对你的数据表结构，我分享几个比现有临时方案更高效的实现思路：

1. 动态SQL+可更新视图（后端驱动） #

如果你的数据库支持动态SQL（比如SQL Server、PostgreSQL），可以通过动态生成交叉表的方式，创建一个可编辑的视图，让前端直接绑定视图进行编辑：

步骤： #

• 首先从tblTests中获取所有测试名称，动态生成PIVOT语句：

  -- SQL Server示例
  DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX)
  -- 拼接所有测试名称为带引号的列名
  SELECT @cols = STRING_AGG(QUOTENAME(testName), ',') FROM tblTests
  -- 生成动态PIVOT查询并创建视图
  SET @query = N'
  CREATE OR ALTER VIEW vwTestResultsPivot
  AS
  SELECT projectID, sampleID, ' + @cols + ', resultID
  FROM (
      SELECT tr.projectID, tr.sampleID, t.testName, tr.testResults, tr.id AS resultID
      FROM tblTestResults tr
      JOIN tblTests t ON tr.testID = t.id
  ) src
  PIVOT (
      MAX(testResults) FOR testName IN (' + @cols + ')
  ) pvt'
  EXEC sp_executesql @query
  

• 为视图添加INSTEAD OF UPDATE触发器，把视图中列的编辑操作映射回原表tblTestResults：

  CREATE TRIGGER trg_vwTestResultsPivot_Update
  ON vwTestResultsPivot
  INSTEAD OF UPDATE
  AS
  BEGIN
      -- 遍历每个测试名称列，更新对应的testResults
      DECLARE @testName NVARCHAR(50), @sql NVARCHAR(MAX)
      DECLARE test_cursor CURSOR FOR
          SELECT testName FROM tblTests
      
      OPEN test_cursor
      FETCH NEXT FROM test_cursor INTO @testName
      
      WHILE @@FETCH_STATUS = 0
      BEGIN
          SET @sql = N'
          UPDATE tblTestResults
          SET testResults = i.' + QUOTENAME(@testName) + '
          FROM inserted i
          JOIN tblTestResults tr ON tr.id = i.resultID
          JOIN tblTests t ON tr.testID = t.id
          WHERE t.testName = ''' + @testName + ''''
          EXEC sp_executesql @sql
          FETCH NEXT FROM test_cursor INTO @testName
      END
      
      CLOSE test_cursor
      DEALLOCATE test_cursor
  END
  

这样前端可以直接绑定这个视图，编辑test1/test2/test3列时，触发器会自动更新原表数据，无需额外操作。

2. 前端动态构建表格+隐藏列绑定（前端驱动） #

如果后端动态SQL不太方便，你可以优化你的GridView方案，用隐藏列绑定测试结果ID替代数组记录，更可靠且易于维护：

步骤： #

• 先从tblTests获取所有测试名称，动态为GridView添加对应列（比如test1、test2）；
• 添加一个隐藏列resultID，用来存储tblTestResults.id；
• 加载数据时，将每个测试项的结果填充到对应列，同时把resultID存入隐藏列；
• 编辑完成后，遍历GridView的行，根据隐藏列的resultID直接更新tblTestResults：

  // C# WinForms示例
  foreach (DataGridViewRow row in dataGridView1.Rows)
  {
      if (!row.IsNewRow)
      {
          int resultID = Convert.ToInt32(row.Cells["resultID"].Value);
          // 获取test1列的值并更新
          int test1Val = Convert.ToInt32(row.Cells["test1"].Value);
          using (SqlCommand cmd = new SqlCommand("UPDATE tblTestResults SET testResults = @val WHERE id = @id", conn))
          {
              cmd.Parameters.AddWithValue("@val", test1Val);
              cmd.Parameters.AddWithValue("@id", resultID);
              cmd.ExecuteNonQuery();
          }
          // 同理处理test2、test3列...
      }
  }
  

这种方式比用数组记录更稳定，当测试项新增或删除时，只需动态调整列即可，无需修改数组逻辑。

3. 主从表单联动（简化设计） #

如果转置不是硬性要求，你可以采用主表单+子表单的设计，更符合数据库原生结构：

• 主表单显示projectID和sampleID，提供切换样本的功能；
• 子表单（或DataGridView）以行式显示该样本的所有测试项（testName）和对应的testResults，用户可以直接编辑每行的结果；
• 切换样本时，子表单自动刷新该样本的所有测试结果。

这种方式无需转置，编辑逻辑更简单，用户学习成本低，适合对表格格式要求不是特别严格的场景。

对比你的临时方案 #

• 方案1（列表框双击加载表单）：多步操作会降低编辑效率，直接在表格内编辑体验更好；
• 方案2（GridView+数组记录）：数组容易出现索引错位问题，用隐藏列绑定ID更可靠，维护成本更低。

内容的提问来源于stack exchange，提问作者Ted