Skip to Main Content
  • Questions
  • How to unpivot table data with out displaying column names

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Sushma.

Asked: January 29, 2018 - 9:33 am UTC

Last updated: January 29, 2018 - 3:47 pm UTC

Version: 10g

Viewed 1000+ times

You Asked

Hi Tom,

Am working on Oracle DB and below query

    select        
    t1.id as dbid,
    t2.mid as askid,
    t3.m2idd as amid from
    table1 t1, table2 t2, table3 t3 where
    t1.actid = t2.senid
    and t2.denid = t2.mkid
    );


is resulting data in

dbid       askid         amid
================================
d1           m1            a1




but expected out put is

SOURCE_ID    DEST_ID
========================
m1             d1
a1             d1


I browsed may web sites and found that unpivot will perform the similar kind of operations. So, tried unpivot but that did not fetch us expected results.

select DEST_ID,SOURCE_ID from 
    (select        
    t1.id as dbid,
    t2.mid as askid,
    t3.m2idd as amid from
    table1 t1, table2 t2, table3 t3 where
    t1.actid = t2.senid
    and t2.denid = t2.mkid
    )
    unpivot INCLUDE NULLS (SOURCE_ME_GUID FOR DEST_ME_GUID IN (amid, askid));


Please suggest on this.

Regards,
Sushma

and Chris said...

The same way as a regular query:

List out the column you want in your select clause!

Dbid isn't in your unpivot clause. So it's still a column in your result set. Include that and exclude the generated dest_id:

select dbid, SOURCE_ID 
from (
  select 'd1' dbid, 'm1' askid, 'a1' amid
  from   dual
) unpivot INCLUDE NULLS (
  SOURCE_ID FOR DEST_ID IN (amid, askid)
);

DBID   SOURCE_ID   
d1     a1          
d1     m1  

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Sushma Kudum, January 29, 2018 - 3:17 pm UTC

Hi Chris,

Thank you for prompt reply, I tried query as mentioned by you and faced below mentioned error:

ORA-01790: expression must have same datatype as corresponding expression

at the step:
SOURCE_ID FOR DEST_ID IN (amid, askid).

all these dbid,amid,askid are of type NOT NULL RAW(16 BYTE).
Chris Saxon
January 29, 2018 - 3:47 pm UTC

Raw seems fine to me:

select dbid, SOURCE_ID 
from (
  select hextoraw('d1') dbid, hextoraw('f1') askid, hextoraw('a1') amid
  from   dual
) unpivot INCLUDE NULLS (
  SOURCE_ID FOR DEST_ID IN (amid, askid)
);

DBID   SOURCE_ID   
D1     A1          
D1     F1  


Sooo....

Give us a test case! i.e.

- create table
- insert into
- your query

Sushma Kudum, January 29, 2018 - 5:24 pm UTC

Thanks a lot Chris,

It worked with minor changes.

Solution is:

select dbid as DEST_ID, SOURCE_ID
from (
select 'd1' dbid,
cast('m1' as varchar2(200)) as askid,
cast('a1' as varchar2(200)) as amid
from dual
) unpivot INCLUDE NULLS (
SOURCE_ID FOR DEST_ID IN (amid, askid)
);


Regards,
Sushma

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.