SQL Column to row conversion

问题:

I have a table with the following structure:

Col1    Col2
---------------
1        2    
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.

回答1:

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.

回答2:

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

回答3:

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