Display data of a column as a row in Oracle

First of all, I would like to thank you so much for your time in advance.

I have a table as shown below:

COL_TAB
----------------------------------------------
| TABLE_NAME | COL_NAME   | COL_DESC         | 
----------------------------------------------
| TABLE1     | TAB1_COL_2 | TABLE 1 COLUMN 2 |
| TABLE1     | TAB1_COL_4 | TABLE 1 COLUMN 4 |
| TABLE1     | TAB1_COL_3 | TABLE 1 COLUMN 3 |
| TABLE1     | TAB1_COL_5 |                  |
| TABLE1     | TAB1_COL_1 | TABLE 1 COLUMN 1 |
----------------------------------------------

​x
 
COL_TAB----------------------------------------------| TABLE_NAME | COL_NAME   | COL_DESC         | ----------------------------------------------| TABLE1     | TAB1_COL_2 | TABLE 1 COLUMN 2 || TABLE1     | TAB1_COL_4 | TABLE 1 COLUMN 4 || TABLE1     | TAB1_COL_3 | TABLE 1 COLUMN 3 || TABLE1     | TAB1_COL_5 |                  || TABLE1     | TAB1_COL_1 | TABLE 1 COLUMN 1 |----------------------------------------------​

I would like to display the data from the above table as shown below:

------------------------------------------------------------------------------------------------
| TABLE 1 COLUMN 2 | TABLE 1 COLUMN 1 | TAB1_COL_5       | TABLE 1 COLUMN 2 | TABLE 1 COLUMN 4 |
------------------------------------------------------------------------------------------------

 
------------------------------------------------------------------------------------------------| TABLE 1 COLUMN 2 | TABLE 1 COLUMN 1 | TAB1_COL_5       | TABLE 1 COLUMN 2 | TABLE 1 COLUMN 4 |------------------------------------------------------------------------------------------------​

If no data is present within the COL_DESC for a row then the data from the COL_NAME column needs to be displayed.

I did ask a similar question here, but the criteria for that were fairly complex. I Would like to know how to pivot a single column of data to a row in the simplest way.

Below is the code I tried.

SELECT NVL(COL_DESC, COL_NAME) 
FROM
(
 SELECT NVL(COL_DESC, COL_NAME), COL_NAME
 FROM COL_TAB
 WHERE TABLE_NAME = 'TABLE1'
)
PIVOT
(
 MIN(COL_NAME)
 FOR COL_NAME IN (SELECT COL_NAME FROM COL_TAB WHERE TABLE_NAME = 'TABLE1'
)

 
SELECT NVL(COL_DESC, COL_NAME) FROM( SELECT NVL(COL_DESC, COL_NAME), COL_NAME FROM COL_TAB WHERE TABLE_NAME = 'TABLE1')PIVOT( MIN(COL_NAME) FOR COL_NAME IN (SELECT COL_NAME FROM COL_TAB WHERE TABLE_NAME = 'TABLE1')​​

Would really be thankful if anyone could help me with getting this problem solved. Once again thank you so much for your time and help.

Answer

As far i know we cannot use query in pivot clause (it may be possible only with XML when we need the IN as dynamic which I am not much familiar) , however by specifying the col_name explicitly we can achieve it,

SELECT MAX(tab1_col_1)
      ,MAX(tab1_col_2)
      ,MAX(tab1_col_3)
      ,MAX(tab1_col_4)
      ,MAX(tab1_col_5)
FROM   (SELECT coalesce(col_desc
                       ,col_name) col_desc
              ,col_name
              ,table_name
        FROM   col_tab
        WHERE  table_name = 'TABLE1')
PIVOT
(
 MIN(col_desc)
 FOR col_name IN('TAB1_COL_1' AS tab1_col_1
                ,'TAB1_COL_2' tab1_col_2
                ,'TAB1_COL_3' tab1_col_3
                ,'TAB1_COL_4' tab1_col_4
                ,'TAB1_COL_5' tab1_col_5)
)

 
SELECT MAX(tab1_col_1)      ,MAX(tab1_col_2)      ,MAX(tab1_col_3)      ,MAX(tab1_col_4)      ,MAX(tab1_col_5)FROM   (SELECT coalesce(col_desc                       ,col_name) col_desc              ,col_name              ,table_name        FROM   col_tab        WHERE  table_name = 'TABLE1')PIVOT( MIN(col_desc) FOR col_name IN('TAB1_COL_1' AS tab1_col_1                ,'TAB1_COL_2' tab1_col_2                ,'TAB1_COL_3' tab1_col_3                ,'TAB1_COL_4' tab1_col_4                ,'TAB1_COL_5' tab1_col_5))​

Is this what you are looking for ?

Advertisement

Answer