Re: sql help, please

  • From: Michael Moore <michaeljmoore@xxxxxxxxx>
  • To: eugene.pipko@xxxxxxxxxxxx
  • Date: Tue, 26 Jan 2010 14:55:19 -0800

Looks like you already got your answer but ...

CREATE TABLE C
(
  ANIMAL  VARCHAR2(5 BYTE),
  GRP     NUMBER(6)
);

Insert into C   (ANIMAL, GRP) Values   ('cat', 1);
Insert into C   (ANIMAL, GRP) Values   ('frog', 1);
Insert into C   (ANIMAL, GRP) Values   ('frog', 66);
Insert into C   (ANIMAL, GRP) Values   ('cat', 33);
Insert into C   (ANIMAL, GRP) Values   ('cat', 44);
Insert into C   (ANIMAL, GRP) Values   ('cat', 66);
Insert into C   (ANIMAL, GRP) Values   ('frog', 44);
Insert into C   (ANIMAL, GRP) Values   ('toad', 44);
COMMIT;

SELECT     animal, LTRIM( SYS_CONNECT_BY_PATH( grp, ',' ), ',' )
      FROM ( SELECT animal, ROW_NUMBER( ) OVER( PARTITION BY animal ORDER BY
1 ) rnum,  grp
              FROM c )
     WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY animal = PRIOR animal AND   rnum - 1 = PRIOR rnum
START WITH rnum = 1;

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 01-2010