SQL Column to row conversion

I have a table with the following structure:

Col1    Col2
---------------
1        2开发者_Go百科    
3        4

I need to Query in the such a way that the output should be like:

ColumnName |  ColumnValue
----------------------------
Col1          1
Col2          2
Col1          3
Col2          4

Any help in this will be greatly appreciated. Thanks in advance.

It's been clarified that the output must be ordered so that it alternates between col1 and col2. Col1 will always be displayed first, even if col2's value is lower. This:

Col1  |  Col2 
------------
11    |  2 
30    |  42 

..should return:

ColumnName  |  ColumnValue
----------------------------
col1        |  11 
col2        |  2 
col1        |  30 
col2        |  42

Effectively, an alternating list based on rank.

It's not clear what database the OP is using. Assuming MySQL, which has no ranking/analytical functionality you can use:

  SELECT x.* 
    FROM (SELECT 'Col1' AS ColumnName,
                 a.col1 AS ColumnValue,
                 @rowcol1 := @rowcol1 + 1 AS rank
            FROM TABLE a
            JOIN (SELECT @rowcol1 := 0) r
          UNION ALL
          SELECT 'Col2',
                 b.col2,
                 @rownum := @rownum + 1
            FROM TABLE b
            JOIN (SELECT @rownum := 0) r) x
ORDER BY x.rank, x.columnname

SQL Server 2005+ and Oracle 9i+ support analytic functions, so you can use ROW_NUMBER or RANK:

  SELECT x.* 
    FROM (SELECT 'Col1' AS ColumnName,
                 a.col1 AS ColumnValue,
                 ROW_NUMBER() OVER(ORDER BY a.col1) AS rank
            FROM TABLE a
          UNION ALL
          SELECT 'Col2',
                 b.col2,
                 ROW_NUMBER() OVER(ORDER BY b.col2) AS rank
            FROM TABLE b) x
ORDER BY x.rank, x.columnname

Previously, based on the provided example data:

SELECT 'Col1' AS ColumnName, 
       a.col1 AS ColumnValue
  FROM TABLE a
UNION ALL
SELECT 'Col2', 
       b.col2
  FROM TABLE b
ORDER BY ColumnValue

UNION ALL returns all rows, while UNION would remove duplicates.

You can also try UNPIVOT

DECLARE @Table TABLE(
        Col1 INT,
        Col2 INT 
)

INSERT INTO @Table SELECT 1,2
INSERT INTO @Table SELECT 3,4

SELECT ColumnName, ColumnValue
FROM    (
            SELECT Col1, Col2
            FROM    @Table
        ) p
UNPIVOT
    (
        ColumnValue FOR ColumnName IN (Col1, Col2)
    ) upvt

Fixed the ordering issue from OMG's solution:

SELECT 'Col1' AS ColumnName, 
       a.col1 AS ColumnValue
  FROM TABLE a
UNION ALL
SELECT 'Col2', 
       b.col2
  FROM TABLE b
ORDER BY ColumnName, ColumnValue