Split a single row into multiple rows using SQL

Question

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.

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.

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

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:

( 4th column, 1st column, 5th column, 6th column ),
( 4th column, 2nd column, 7th column, 8th column ),
( 4th column, 3rd column, 9th column, 10th column )

My current SQL:

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 > 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 > 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 > A.Col1
AND A.Col1 = 0 

Answer 1

The following should do the trick. And I does it with a single pass over the data.

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
    A_Col2 INT,
    A_Col4 INT,
    A_Col6 INT, 
    B_Col1 INT,
    B_Col2 INT,
    B_Col3 INT, 
    B_Col4 INT,
    B_Col5 INT,
    B_Col6 INT,
    B_Col7 INT 
    );
INSERT #TestData (A_Col2, A_Col4, A_Col6, B_Col1, B_Col2, B_Col3, B_Col4, B_Col5, B_Col6, B_Col7) VALUES
    (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);

--====================================================================

SELECT 
    Constructioncode = td.B_Col1,
    ab.OccupancyCode, 
    ab.MappingID, 
    ab.DamageFactor
FROM
    #TestData td
    CROSS APPLY ( VALUES 
                        (td.A_Col2, td.B_Col2, td.B_Col3), 
                        (td.A_Col4, td.B_Col4, td.B_Col5), 
                        (td.A_Col6, td.B_Col6, td.B_Col7) 
                    ) ab (OccupancyCode, MappingID, DamageFactor)
ORDER BY
    ab.OccupancyCode,
    td.B_Col1;

Results...

Constructioncode OccupancyCode MappingID   DamageFactor
---------------- ------------- ----------- ------------
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