SQL Server 视图 - 模拟来自规范化源的表

Question

好吧，我有一个需要存储在数据库中的表。该表采用以下格式：

Value  | No Deductible | $100 | $250 | $500 |
=============================================
$20000 |          $122 |  $61 |  $28 |  N/A |
$30000 |          $183 | $117 |  $67 |  $44 |

为了使其规范化并易于访问，该表在 SQL Server 数据库中将采用以下格式：

Value (PK)  | Deductible (PK) | Cost |
======================================
     $20000 |              $0 | $122 | 
     $20000 |            $100 |  $61 |  

Value 和 Deductible 列是联合主键，防止重复。此外，还会有一个限制，只允许某些免赔额。

现在，这种格式非常适合可访问性，但插入数据显然并不容易。我没有立即计划为其构建逻辑前端，因此这种情况下的视图将是理想的。但是，我尝试生成可以轻松编辑的视图的尝试失败了。

尝试 1

SELECT 
    t1.Value, t2.Cost AS [No Deductible], 
    t3.Cost AS [$100], t4.Cost AS [$250], t5.Cost AS [$500]
FROM  Valuation AS t1 
INNER JOIN Valuation AS t2 ON t1.Value = t2.Value 
INNER JOIN Valuation AS t3 ON t2.Value = t3.Value 
INNER JOIN Valuation AS t4 ON t3.Value = t4.Value 
INNER JOIN Valuation AS t5 ON t1.Value = t5.Value
WHERE  
    (t2.Deductible = 0) AND (t3.Deductible = 100) 
    AND (t4.Deductible = 250) AND (t5.Deductible = 500)  

这不起作用，因为您无法修改多个基表。

尝试 2

SELECT Value, 
   (SELECT Cost FROM Valuation WHERE Deductible = 0) AS [No Deductible],
   (SELECT Cost FROM Valuation WHERE Deductible = 100) AS [$100],
   (SELECT Cost FROM Valuation WHERE Deductible = 250) AS [$250],
   (SELECT Cost FROM Valuation WHERE Deductible = 500) AS [$500]
FROM Valuation 
GROUP BY Value

您也无法修改计算列。

这个问题有优雅的解决方案吗？也许与工会有关？我觉得我现在唯一的解决方案是为每个免赔额生成多个视图，保留原始表布局（一种可能性，但不是首选，因为价值/免赔额键在其他表中用作外键）或者只是投资创建逻辑前端来模拟表（正如我所说，我更愿意稍后再做）。

Answer 1

首先，查询的另一个选择是：

Select Value
    , Min( Case When Deductible = '0' Then Cost End ) As [No Deductible]
    , Min( Case When Deductible = '100' Then Cost End ) As [$100]
    , Min( Case When Deductible = '250' Then Cost End ) As [$250]
    , Min( Case When Deductible = '500' Then Cost End ) As [$500]
From Valuation
Group By Value

但是，这不会使视图可更新。使其可更新的唯一方法是添加一个 ReplaceOf 触发器，该触发器会将视图结构中的输入行重新规范化回其规范化存储格式。