how to Normalize the sql table in two tables or one detailed table

Question

I would like little help with my SQL normalization. I have a table with as follows:

tbl_code

Ucode, desc, code1, code2, code3
1      aa    1      1      1
2      bb    1      2      2
3      cc    1      1      1

Now I want to make this table to normalized as:

ucode, desc, code,  value
1      aa    code1  1     
2      bb    code1  1    
3      cc    code1  1
1      aa    code2  1

and so on...

How can I do this in SQL? Can someone please help me out?

Answer 1

Yet another option

Select A.UCode
      ,A.[Desc]
      ,B.*
 From tbl_code A
 Cross Apply (
               values ('code1',code1)
                     ,('code2',code2)
                     ,('code3',code3)
             ) B(code,value)

Updated - Dynamic without using Dynamic SQL

Select A.Ucode
      ,A.[desc]
      ,C.*
 From  tbl_code A
 Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)
 Cross Apply (
                Select Code  = a.value('local-name(.)','varchar(100)')
                      ,Value = a.value('.','varchar(max)') 
                 From  B.XMLData.nodes('/row')  as C1(n)
                 Cross Apply C1.n.nodes('./@*') as C2(a)
                 Where a.value('local-name(.)','varchar(100)') not in ('Ucode','desc')
                 -- {or you can use} Where a.value('local-name(.)','varchar(100)') like 'code%'
             ) C