从非规范化表加载规范化表

Question

我的桌子结构如下所示：

ID|Letter1|Letter2|Letter3|...n
-------------------------------
1    A       C       NULL  ...

2    A       D       D

3    P       F       M

使用select，我必须将它加载到一个更规范化的表中，如：

ID|Letter|
1    A
1    C
1    NULL
1    ...
2    A
2    D
2    D
3    P
3    F
3    M

我尝试过不同的方法：

INSERT INTO Normalized
 (ID, Letter)
SELECT ID, LETTER1
FROM Denormalized

一遍又一遍地重复这封信的每一次。我的问题是，我有超过100个字母字段，这个糟糕的设计，我已经尝试了我的头脑围绕不同的想法，但没有什么结果。

我的问题是，有人知道一种动态的方法，在为每个字母创建不同行的同时，将每个字母从非规范化表插入到规范化表中吗？

Answer 1

Declare @XML xml = (Select * from Denormalized for XML RAW)

INSERT INTO Normalized
Select ID     = r.value('@ID','int')
      --,Item   = attr.value('local-name(.)','varchar(100)')  -- Removed but will return Field Name
      ,Letter  = attr.value('.','varchar(max)') 
 From  @XML.nodes('/row') as A(r)
 Cross Apply A.r.nodes('./@*') AS B(attr)
 Where attr.value('local-name(.)','varchar(100)') not in ('ID')

返回(带有XML的动态版本省略了NULLS)

ID  Letter
1   A
1   B
2   A
2   D
2   D
3   P
3   F
3   M

编辑-为了保持NULLS，另一种选择是使用交叉应用

INSERT INTO Normalized
Select A.ID
      ,B.Letter
 From  Denormalized A
 Cross Apply (Values 
              (A.Letter1)
             ,(A.Letter2)
             ,(A.Letter3)
             --...
             --,(A.LetterN)
             ) B (Letter)

返回

ID  Letter
1   A
1   B
1   NULL   << We kept the NULL
2   A
2   D
2   D
3   P
3   F
3   M