Re: Challenging rows to columns problem

From: Barbara Boehmer <baboehme_at_hotmail.com>
Date: 10 Jun 2005 16:41:00 -0700
Message-ID: <1118446859.970506.139480@g49g2000cwa.googlegroups.com>

scott_at_ORA92> SELECT * FROM test_tab
  2 /

COL COL
--- ---

X a
X b
Y c
Y d
Y e
Z f
Z g

7 rows selected.

scott_at_ORA92> SELECT x.col2 AS "X", y.col2 AS "Y", z.col2 AS "Z"   2 FROM (SELECT col2,

  3  		    ROW_NUMBER () OVER (ORDER BY col2) AS rn
  4  	     FROM   test_tab WHERE col1 = 'X') x,
  5  	    (SELECT col2,
  6  		    ROW_NUMBER () OVER (ORDER BY col2) AS rn
  7  	     FROM   test_tab WHERE col1 = 'Y') y,
  8  	    (SELECT col2,
  9  		    ROW_NUMBER () OVER (ORDER BY col2) AS rn
 10  	     FROM   test_tab WHERE col1 = 'Z') z,
 11  	    (SELECT DISTINCT ROW_NUMBER () OVER
 12  		      (PARTITION BY col1 ORDER BY col2) AS rn
 13  	     FROM   test_tab) t
 14  WHERE  t.rn = x.rn (+)

 15 AND t.rn = y.rn (+)
 16 AND t.rn = z.rn (+)
 17 /

X Y Z
--- --- ---

a c f
b d g

    e

scott_at_ORA92> Received on Fri Jun 10 2005 - 18:41:00 CDT