Original
    RecordKey   Name Section1_Product   Section1_Code   Section2_Product   Section2_Code ......
    1           a         ff              22
    2           b         gg              22
    3           c         hh              33


    RecordKey     Name      Section     Product      Code ......
    1               a           1         ff          22
    1               a           2
    2               b           1         gg          22
    2               b           2
    3               c           1         hh          22
    3               c           2

SELECT RecordKey
 ,Name
 ,'Num_of_Sections' = ROW_NUMBER() OVER (PARTITION BY RecordKey ORDER BY ID)
 ,Product
 ,Code
FROM (
SELECT RecordKey, Name, Section1_Product, Section1_Code, Section2_Product, Section2_Code FROM Table
) M

UNPITVOT (
  Product FOR ID IN (Section1_Product, Section2_Product)
) p

UNPIVOT (
  Code FOR CO IN (Section1_Code, Section2_Code)
) c