CODEWITHSUNDEEP
sqldatabasepivot
crazy developer
crazy developer

Reputation: 1

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.

Upvotes: 0

Views: 1603

Answers (3)

OMG Ponies
OMG Ponies

Reputation: 332521

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.

Upvotes: 5

Jonas Lincoln
Jonas Lincoln

Reputation: 9757

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

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166336

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

Upvotes: 1

Related Questions