Re: Not sure how to write this in sql

  • From: Vladimir Begun <Vladimir.Begun@xxxxxxxxxx>
  • To: "Oracle-L (E-mail)" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 12 Jan 2005 14:45:36 -0800

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

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

Other related posts: