SQL: Convert multiple columns to rows

Converting columns to rows is called unpivoting. Converting rows to columns is pivoting.

One approach to unpivoting data is to combine the apply operator with a table value constructor.

This example uses a common table expression (CTE) to return 3 sample records.

Id  ColumnOne   ColumnTwo   ColumnThree
1   a           b           c
2   d           e           f
3   g           h           i

Example

-- Unpivoting with apply and VALUES.        
WITH SampleData AS
    (
        /* This CTE returns 3 sample records.
         */             
        SELECT
            cte.*
        FROM
            (
                VALUES
                    (1, 'a', 'b', 'c'),
                    (2, 'd', 'e', 'f'),
                    (3, 'g', 'h', 'i')
            ) AS cte(Id, ColumnOne, ColumnTwo, ColumnThree)
    )
SELECT
    sd.Id,
    ca.*
FROM
    SampleData AS sd
        CROSS APPLY
            (
                VALUES
                    (ColumnOne),
                    (ColumnTwo),
                    (ColumnThree)
            ) AS ca (ColumnFour)
;

The returned output looks like this:

Id  ColumnFour
1   a
1   b
1   c
2   d
2   e
2   f
3   g
3   h
3   i

Personally I prefer @nazark's approach. Using the UNPIVOT operator helps others to follow the intention of your code. If that answer helped you please accept it. Accepting answers rewards the contributor for his/her efforts and helps other people with the same problem to find a working answer.