Transform fields into rows without a stored procedure

In a legacy table in sql server 2005 I have settings for a customizable grid of data defined like this

TotalColumn (int, null)
Column1Name (char(12), null)
Column2Name (char(12), null)
Column3Name (char(12), null)
Column4Name (char(12), null)
Column5Name (char(12), null)
Column1Type (int, null)
Column2Type (int, null)
...

Where TotalColumn is an integer 0-5 noting which column is totaled. Instead of building an object model to mimic this, I want to convert this data into a list of column definitions, ordered by their index, like this

public class LedgerColumn
{
    public int Index { get; set; }
    public bool IsVisible { get; set; }
    public string Name { get; set; }
    public LedgerColumnType ColumnType { get; set; }
    public bool IsTotaled { get; set; }
}

I want to do so without using a stored procedure. In the past I have done data manipulation using cte and then pivot the data in a view. I'm just not sure how to go about splitting开发者_开发技巧 the fields out into rows without doing something like

SELECT
    Column1Name,
    Column1Type,
    ...
FROM
    MyTable

UNION

SELECT
    Column2Name,
...

Something tells me there has to be a better way, I'm just not sure how to go about it

Sounds like you're wanting to do the opposite of a Pivot - an Unpivot! They can be somewhat confusing, but I think that's what you're looking for.

From MSDN's TSQL Pivot and Unpivot:

UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.