Rajya Lakshmi Jampani, January 04, 2016 - 9:22 am UTC
The introduction of "With t as" tag was useful. Colud have been better if an alternative was suggested instead of Unpivot as Pivot/Unpivot is introduced in Oracle 11g.
January 04, 2016 - 11:28 am UTC
Ah, apologies for that. In that case, I think you're back to UNION / UNION ALL.
Just Cross join.
Rajeshwaran, Jeyabal, January 04, 2016 - 12:34 pm UTC
rajesh@ORA10G> set feedback off
rajesh@ORA10G> drop table t purge;
rajesh@ORA10G> create table t(x varchar2(5),y varchar2(5));
rajesh@ORA10G> insert into t values('AB','CD');
rajesh@ORA10G> insert into t values('EF','GH');
rajesh@ORA10G> insert into t values('IJ','KL');
rajesh@ORA10G> commit;
rajesh@ORA10G>
rajesh@ORA10G> set feedback on
rajesh@ORA10G> select decode(r,1,x,2,y)
2 from t, (select level r
3 from dual
4 connect by level <=2)
5 order by t.rowid
6 /
DECOD
-----
AB
CD
GH
EF
KL
IJ
6 rows selected.
rajesh@ORA10G>
With MODEL Clause in 10g
Rajeshwaran, Jeyabal, January 04, 2016 - 12:38 pm UTC
rajesh@ORA10G> select z
2 from t
3 model
4 partition by (rowid)
5 dimension by (1 c1)
6 measures ( cast(null as varchar2(5)) z,x,y)
7 rules
8 ( z[0] = x[1] ,
9 z[1] = y[1] )
10 order by rowid ,z
11 /
Z
-----
AB
CD
EF
GH
IJ
KL
6 rows selected.
rajesh@ORA10G>