title: Convert Multi-Columns to Rows
body: <p>I have a legacy table with the following columns.</p>
  
  <pre><code>Create Table test
  ( id varchar(50)
  , code1 varchar(50)
  , codeDesc1 varchar(50)
  , code2 varchar(50)
  , codeDesc2 varchar(50)
  , code3 varchar(50)
  , codeDesc3 varchar(50)
  , code4 varchar(50)
  , codeDesc4 varchar(50)
  , code5 varchar(50)
  , codeDesc5 varchar(50)
  , code6 varchar(50)
  , codeDesc6 varchar(50)
  )
  
  insert into test (id, code1, codeDesc1, code2, codeDesc2, code3, codeDesc3, code4, codeDesc4, code5, codeDesc5, code6, codeDesc6)
  values ('1','100.1', 'first code','100.2', 'Second code','100.3', 'Third code','100.4', 'Fourth code','100.5', 'Fifth code','100.6', 'Sixth code')
  
  insert into test (id, code1, codeDesc1, code2, codeDesc2, code3, codeDesc3, code4, codeDesc4, code5, codeDesc5, code6, codeDesc6)
  values ('2','100.1', 'first code','100.2', 'Second code','100.3', 'Third code','100.4', 'Fourth code','100.5', 'Fifth code','100.6', 'Sixth code')
  
  select * from test
  </code></pre>
  
  <p>returns as following:</p>
  
  <pre><code>id, code1, codeDesc1, code2, codeDesc2, code3, codeDesc3, code4, codeDesc4, code5, codeDesc5, code6, codeDesc6
  
  1,100.1, first code,100.2, Second code,100.3, Third code,100.4, Fourth code,100.5, Fifth code,100.6, Sixth code
  
  2,100.1, first code,100.2, Second code,100.3, Third code,100.4, Fourth code,100.5, Fifth code,100.6, Sixth code
  </code></pre>
  
  <p>But i want to get the data back as shown below:</p>
  
  <pre><code>ID, Code, Description
  
  1, 100.1, First Code
  1, 100.2, Second Code
  1, 100.3, Third Code
  1, 100.4, Fourth Code
  1, 100.5, Fifth Code
  1, 100.6, Sixth Code
  2, 100.1, First Code
  2, 100.2, Second Code
  2, 100.3, Third Code
  2, 100.4, Fourth Code
  2, 100.5, Fifth Code
  2, 100.6, Sixth Code
  </code></pre>
  
  <p>Can anyone help me how to do that, please?</p>
  
  <p>I have tried the UNPIVOT but did not give me the exact result that i was looking for.  Here is the query i used but needs to be tweaked to get the exact result where I am finding the difficult time.</p>
  
  <pre><code>SELECT Id, FieldCode , FieldValue
      FROM 
      (
          SELECT  id, code1, codeDesc1, code2, codeDesc2, code3, codeDesc3, code4, codeDesc4, code5, codeDesc5, code6, codeDesc6
          FROM test
      ) MyTable
      UNPIVOT
      (FieldValue FOR FieldCode IN (code1, codeDesc1, code2, codeDesc2, code3, codeDesc3, code4, codeDesc4, code5, codeDesc5, code6, codeDesc6))AS MyUnPivot
  </code></pre>
  
  <p>Thank for your help in advance.</p>