Friday, 15 May 2015

Oracle query to convert multiple column into one column -


I have 50 columns in the table and it only gives one line and I think in a row with 50 columns 50 rows and one column should be displayed

Can anyone recommend me the Oracle query for this?

You can use UNPIVOT for a line like this To get only columns with

  SELECT colvalue FROM (SELECT * FROM TABLE1 UNPIVOT contains NULLS (colvalue col col IN (col1, col2, col3, col4, col5, col6, col7 , Col8, col9, col10, ... col50))); Sample output:  
 | COLVALUE | ------------ | 1 | | 2 | | (Faucet) | | .......... |  

If you need the column with the names of the columns then select your external table

  SELECT * from Table1 UNPIVOT (cols IN (col1 for columns) , Col2, col3, col4, col5, col6, col7, col8, col9, col10, ... col50)); Sample output:  
 | COLS | COLVALUE | -------------------- | COL1 | 1 | | COL2 | 2 | | COL3 | (Faucet) | | ..... ......... |  

Here is the demo

No comments:

Post a Comment