title: Transforming Columns to Rows in SQL
body: <p>I have a scenario in one of the implementations to be done on Hive for reporting. I have a table structure that currently looks as below - </p>
  
  <pre><code>+------+------+----------+----------+-------+-------+--------+--------+
  | Col1 | Col2 | M1_Today | M2_Today | M1_LW | M2_LW | M1_L2W | M2_L2W |
  +------+------+----------+----------+-------+-------+--------+--------+
  | A    | A1   |       10 |      200 |     9 |   190 |     11 |    210 |
  | A    | A2   |       12 |      210 |    11 |   200 |     13 |    220 |
  | B    | B1   |       15 |      300 |    14 |   290 |     16 |    310 |
  | B    | B2   |       18 |      310 |    17 |   300 |     19 |    320 |
  +------+------+----------+----------+-------+-------+--------+--------+
  </code></pre>
  
  <p>The columns in the table need to be transformed to appear as below - </p>
  
  <pre><code>+------+------+-------+----+-----+
  | Col1 | Col2 | Col3  | M1 | M2  |
  +------+------+-------+----+-----+
  | A    | A1   | Today | 10 | 200 |
  | A    | A1   | LW    |  9 | 190 |
  | A    | A1   | L2W   | 11 | 210 |
  | A    | A2   | Today | 12 | 210 |
  | A    | A2   | LW    | 11 | 200 |
  | A    | A2   | L2W   | 13 | 220 |
  | B    | B1   | Today | 15 | 300 |
  | B    | B1   | LW    | 16 | 310 |
  | B    | B1   | L2W   | 14 | 290 |
  | B    | B2   | Today | 18 | 310 |
  | B    | B2   | LW    | 17 | 300 |
  | B    | B2   | L2W   | 19 | 320 |
  +------+------+-------+----+-----+
  </code></pre>
  
  <p>How can this be achieved via SQL. I am using HIVE as my datastore. Any help is much appreciated</p>