Re: sql help, please

From: Michael Moore <michaeljmoore_at_gmail.com>
Date: Tue, 26 Jan 2010 14:55:19 -0800
Message-ID: <26fdee6e1001261455q339f28c4g9b41631062b24ec7_at_mail.gmail.com>

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;

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

Received on Tue Jan 26 2010 - 16:55:19 CST