Загрузка нормализованной таблицы из денормализованной таблицы

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

РЕДАКТИРОВАТЬ - Чтобы сохранить значения NULL, другой вариант заключается в использовании CROSS APPLY

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