CODEWITHSUNDEEP
sqloracle-databasepivot
joe
joe

Reputation: 173

How to include column names along with rows in a result set in oracle

I am trying to insert the column header and corresponding field into another table .

Table1 :

col1    col2   col3   col4
1       2       3      4

output should look like this:

COL_A   COL_B   COL_C     COL_D    COL_E     COL_F      COL_G    COL_H 
col1    1       col2       2       col3       3         col4     4

I tried to apply unpivot, but unpivot gives me the column names vertically not horizontally.

Upvotes: 3

Views: 2044

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Doesn't this do what you want?

select 'col1' as col_a, col1 as col_b,
       'col2' as col_c, col2 as col_d,
       'col3' as col_e, col3 as col_f,
       'col4' as col_g, col4 as col_h
from t;

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

Try this. Add appropriate aliases from this result however you want.

SELECT * FROM
 (
    SELECT *
    FROM Table1
    UNPIVOT(val FOR col IN (
                COL1
                ,COL2
                ,COL3
                ,COL4
                ))
    )
PIVOT(  MAX(COl) as C, MAX(VAL) as V FOR COL IN (
             'COL1' as VAL1
            ,'COL2' as VAL2
            ,'COL3' as VAL3
            ,'COL4' as VAL4
            ));

Demo

Upvotes: 2

Related Questions