title: Dynamic Unpivot and split Columns SQL Server 2012
body: <p>I have a table as MarketOutput that has 20 Columns</p>
  
  <pre><code>[Region] [LOB]  [GWP 2013]  [GWP 2014]  [LR 2013]   [LR 2014]
  -------------------------------------------------------------
  North  Workers  38902.50     37,972,404   89             82
  </code></pre>
  
  <p>I would like to dynamically change column to rows. Region and LOB are fixed columns, [GWP 2013], [GWP 2014], [LR 2013], [LR 2014] are dynamic columns. </p>
  
  <p>For next year they will be <code>[GWP 2015]</code>, <code>[LR 2015]</code></p>
  
  <p>I want to unpivot the column and split the [GWP 2014] as two column [GWP], [2014].</p>
  
  <p>The output should be like</p>
  
  <pre><code>Region  [LOB]      [Metrics] [Year] [Value]
  --------------------------------------------------
  North   Workers    GWP       2013         38902.50 
  North   Workers    GWP       2014    37,972,404
  North   Workers    LR        2013            89
  North   Workers    LR        2014            82
  </code></pre>
  
  <p>Can you please suggest how it can be done?</p>
  
  <p>I am new to pivoting in SQL Server</p>
  
  <p>I would also like to insert the output into a new table every time with dynamic list</p>