CODEWITHSUNDEEP
oracle-database
user1881820
user1881820

Reputation: 157

Oracle query to convert multiple column into one column

I have 50 column in a table and it returns only one row and I want that one row with 50 column to be displayed in 50 rows and one column.

Can any one suggest me the Oracle query for it?

Upvotes: 1

Views: 5748

Answers (2)

peterm
peterm

Reputation: 92785

You can use UNPIVOT for one row like this to get only column with values

SELECT colvalue 
  FROM
(
  SELECT * 
    FROM Table1
  UNPIVOT INCLUDE NULLS
  (
    colvalue FOR cols IN (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, ... col50)
  )
);

Sample output:

| COLVALUE |
------------
|        1 |
|        2 |
|   (null) |
|..........|

If you need column with column names from your pivoted table just ditch the outer select

SELECT * 
  FROM Table1
UNPIVOT INCLUDE NULLS
(
  colvalue FOR cols IN (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, ... col50)
);

Sample output:

|  COLS | COLVALUE |
--------------------
|  COL1 |        1 |
|  COL2 |        2 |
|  COL3 |   (null) |
| ..... |......... |

Here is SQLFiddle demo

Upvotes: 4

Ed Gibbs
Ed Gibbs

Reputation: 26333

Be prepared for a lot of typing :) Oracle has UNPIVOT functionality but it wants at least two columns in the result, so it won't work for your situation.

First off, you'll need a counter from 1 to 50. You can query one like this:

SELECT LEVEL as Counter FROM DUAL CONNECT BY LEVEL <= 50

If you execute this query you'll get the numbers 1-50 as your result. With that as a basis, here's the full(ish) query:

WITH Cols AS (
  SELECT LEVEL as Counter
  FROM DUAL
  CONNECT BY LEVEL <= 50
)
SELECT
  CASE Cols.Counter
    WHEN 1 THEN Col1
    WHEN 2 THEN Col2
    WHEN 3 THEN Col3
    . . .
    WHEN 50 THEN Col50
  END AS myColumn
FROM myTable
CROSS JOIN Cols
ORDER BY Cols.Counter

Note that all of the columns must be the same data type, so if you have a mixture of character, number and date you'll need to convert them all to character.

Note that this query assumes one row in the table, as mentioned in the question. If there's more than one row you should end with ORDER BY a-column-that-identifies-the-row, Cols.Counter.

Upvotes: 0

Related Questions