Loading a normalized table from a denormalized table

Question

I have a table structure like the following:

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

2    A       D       D

3    P       F       M

Using select I have to load this into a more normalized table like:

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

I have tried different ways of doing this:

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

And just do that over and over again for each iteration of the letter. My problem with that is that I have over 100 letter fields of this bad design and I have tried to wrap my head around different ideas but nothing has come to fruition.

My question is does anyone know a dynamic way to insert each letter from a denormalized table to a normalized table while creating a different row for each letter?

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')

Returns (Dynamic version with XML omits NULLS)

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

EDIT - To Keep the NULLS, another option is to use a 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)

Returns

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