title: Split a single row into multiple rows using SQL
body: <p>I am trying to generate multiple rows from a single row and was able to do it using the below SQL. But I am wondering if there is a better way to write this SQL as its very long and can't apply to real-time scenarios. Thank you.</p>
  
  <p>Note:- My query would return the desired output but it is not an efficient way 
          to solve the problem. My actual input has more columns and I don't want to write 'n' no. of SQL queries and do a UNION ALL for all of them. 
  Please suggest a better solution. Thank you.</p>
  
  <pre><code>Input :- 
   A.Col2 A.Col4 A.Col6 B.Col1 B.Col2 B.Col3 B.Col4 B.Col5 B.Col6 B.Col7
      300    301    302    100   9011    100   9002    100   9002    100
      300    301    302    101   8101     95   2001    100   2001    100
      300    301    302    102   8101    105   2001    110   2001    110
  
  Desired output :-
  100   300  9011  100
  101   300  8101  95
  102   300  8101  105
  100   301  9002  100
  101   301  2001  100
  102   301  2001  110
  100   302  9002  100
  101   302  2001  100
  102   302  2001  110
  </code></pre>
  
  <p>In my example I have built test data with total 10 fields (First three fields and the remaining seven fields.) For every value in fourth column I have to build three rows with four columns, the three rows would look like:</p>
  
  <pre><code>( 4th column, 1st column, 5th column, 6th column ),
  ( 4th column, 2nd column, 7th column, 8th column ),
  ( 4th column, 3rd column, 9th column, 10th column )
  </code></pre>
  
  <p>My current SQL:</p>
  
  <pre><code>SELECT B.Col1 AS Constructioncode,
         A.Col2 AS OccupancyCode,       
         B.Col2 AS MappingID,
         B.Col3 AS DamageFactor
    FROM 
  ( 
  SELECT 0 AS Col1,300 AS Col2,0 AS Col3,301 AS Col4,0 AS Col5,302 AS Col6, 0 AS Col7 
  UNION ALL
  SELECT 100 AS Col1,9011 AS Col2,100 AS Col3,9002 AS Col4,100 AS Col5,9002 AS Col6,100 AS Col7 
  UNION ALL
  SELECT 101 AS Col1,8101 AS Col2,95 AS Col3,2001 AS Col4,100 AS Col5,2001 AS Col6,100 AS Col7
  UNION ALL
  SELECT 102 AS Col1,8101 AS Col2,105 AS Col3,2001 AS Col4,110 AS Col5,2001 AS Col6,110 AS Col7
  ) A
  CROSS JOIN
  (
  SELECT 0 AS Col1,300 AS Col2,0 AS Col3,301 AS Col4,0 AS Col5,302 AS Col6, 0 AS Col7 
  UNION ALL
  SELECT 100 AS Col1,9011 AS Col2,100 AS Col3,9002 AS Col4,100 AS Col5,9002 AS Col6,100 AS Col7 
  UNION ALL
  SELECT 101 AS Col1,8101 AS Col2,95 AS Col3,2001 AS Col4,100 AS Col5,2001 AS Col6,100 AS Col7
  UNION ALL
  SELECT 102 AS Col1,8101 AS Col2,105 AS Col3,2001 AS Col4,110 AS Col5,2001 AS Col6,110 AS Col7
  ) B
  WHERE B.Col1 &gt; A.Col1
  AND A.Col1 = 0
  UNION ALL
  SELECT B.Col1 AS Constructioncode,
         A.Col4 AS OccupancyCode,       
         B.Col4 AS MappingID,
         B.Col5 AS DamageFactor
    FROM 
  ( 
  SELECT 0 AS Col1,300 AS Col2,0 AS Col3,301 AS Col4,0 AS Col5,302 AS Col6, 0 AS Col7 
  UNION ALL
  SELECT 100 AS Col1,9011 AS Col2,100 AS Col3,9002 AS Col4,100 AS Col5,9002 AS Col6,100 AS Col7 
  UNION ALL
  SELECT 101 AS Col1,8101 AS Col2,95 AS Col3,2001 AS Col4,100 AS Col5,2001 AS Col6,100 AS Col7
  UNION ALL
  SELECT 102 AS Col1,8101 AS Col2,105 AS Col3,2001 AS Col4,110 AS Col5,2001 AS Col6,110 AS Col7
  ) A
  CROSS JOIN
  (
  SELECT 0 AS Col1,300 AS Col2,0 AS Col3,301 AS Col4,0 AS Col5,302 AS Col6, 0 AS Col7 
  UNION ALL
  SELECT 100 AS Col1,9011 AS Col2,100 AS Col3,9002 AS Col4,100 AS Col5,9002 AS Col6,100 AS Col7 
  UNION ALL
  SELECT 101 AS Col1,8101 AS Col2,95 AS Col3,2001 AS Col4,100 AS Col5,2001 AS Col6,100 AS Col7
  UNION ALL
  SELECT 102 AS Col1,8101 AS Col2,105 AS Col3,2001 AS Col4,110 AS Col5,2001 AS Col6,110 AS Col7
  ) B
  WHERE B.Col1 &gt; A.Col1
  AND A.Col1 = 0 
  UNION ALL
  SELECT B.Col1 AS Constructioncode,
         A.Col6 AS OccupancyCode,       
         B.Col6 AS MappingID,
         B.Col7 AS DamageFactor
    FROM 
  ( 
  SELECT 0 AS Col1,300 AS Col2,0 AS Col3,301 AS Col4,0 AS Col5,302 AS Col6, 0 AS Col7 
  UNION ALL
  SELECT 100 AS Col1,9011 AS Col2,100 AS Col3,9002 AS Col4,100 AS Col5,9002 AS Col6,100 AS Col7 
  UNION ALL
  SELECT 101 AS Col1,8101 AS Col2,95 AS Col3,2001 AS Col4,100 AS Col5,2001 AS Col6,100 AS Col7
  UNION ALL
  SELECT 102 AS Col1,8101 AS Col2,105 AS Col3,2001 AS Col4,110 AS Col5,2001 AS Col6,110 AS Col7
  ) A
  CROSS JOIN
  (
  SELECT 0 AS Col1,300 AS Col2,0 AS Col3,301 AS Col4,0 AS Col5,302 AS Col6, 0 AS Col7 
  UNION ALL
  SELECT 100 AS Col1,9011 AS Col2,100 AS Col3,9002 AS Col4,100 AS Col5,9002 AS Col6,100 AS Col7 
  UNION ALL
  SELECT 101 AS Col1,8101 AS Col2,95 AS Col3,2001 AS Col4,100 AS Col5,2001 AS Col6,100 AS Col7
  UNION ALL
  SELECT 102 AS Col1,8101 AS Col2,105 AS Col3,2001 AS Col4,110 AS Col5,2001 AS Col6,110 AS Col7
  ) B
  WHERE B.Col1 &gt; A.Col1
  AND A.Col1 = 0 
  </code></pre>