Re: Not sure how to write this in sql

From: Vladimir Begun <Vladimir.Begun_at_oracle.com>
Date: Wed, 12 Jan 2005 14:45:36 -0800
Message-ID: <41E5A890.9030009@oracle.com>

> DROP TABLE t;
> CREATE TABLE t (grp NUMBER, value VARCHAR2(10));
> INSERT INTO t VALUES(1, 'a');
> INSERT INTO t VALUES(1, 'b');
> INSERT INTO t VALUES(1, 'c');
> INSERT INTO t VALUES(2, 'd');
> INSERT INTO t VALUES(2, 'e');
> COMMIT;
SELECT grp

      , RTRIM(
          MAX(DECODE(rn,  1, value))
       || ','
       || MAX(DECODE(rn,  2, value))
       || ','
       || MAX(DECODE(rn,  3, value))
       || ','
       || MAX(DECODE(rn,  4, value))
        , ','
        ) result
   FROM (
        SELECT ROW_NUMBER() OVER (PARTITION BY grp ORDER BY value) rn
             , grp
             , value
          FROM t
         )

  GROUP BY grp
/

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 12 2005 - 16:46:46 CST