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
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
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
