title: Transposing columns to rows using UNPIVOT
body: <p>I have a table that for some reason has hardcoded values like so:</p>
  
  <pre><code>Row ID    QtyC1   QtyC2  QtyC3   QtyC4  QtyN1   QtyN2  QtyN3   QtyN4  
  100       10      5      8       9      11      12     5       6
  101       9       11     12      5      6       10     4       9
  </code></pre>
  
  <p>The table has 35 columns and around 12k records (meaning around 500k values) and is being added to and amended constantly.</p>
  
  <p>I am trying to transpose this in a view into:</p>
  
  <pre><code>Row ID  Year  Period  Val
  100     C     1       10
  100     C     2       5
  100     C     3       8
  100     C     4       9
  100     N     1       11
  100     N     2       12
  100     N     3       5
  100     N     4       6
  </code></pre>
  
  <p>So far I have managed to split it out into single values using this query:</p>
  
  <pre><code>SELECT Row ID, YP, Val
  
  FROM (SELECT Row ID
      , QtyC1 AS C1
      , QtyC2 AS C2
      , QtyC3 AS C3
      , QtyC4 AS C4
      , QtyN1 AS N1
      , QtyN2 AS N2
      , QtyN3 AS N3
      , QtyN4 AS N4
  
  FROM MyTable
  ) SUB
  UNPIVOT (Val FOR YP IN (C1,C2,C3,C4,N1,N2,N3,N4)) AS PVT
  </code></pre>
  
  <p>This is getting me a single identifying value (eg <code>C1</code>) but how can I split it so I have a numeric period and a single character for the year (<code>1</code> and <code>C</code>)?</p>
  
  <p>I can see it might be possible just splitting up the string into two parts but I was hoping for a cleaner way if possible.</p>