Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Group by comma separated string

584873Nov 16 2010 — edited Nov 17 2010
i all,

Oracle 11 g
I have the following table and data


CREATE TABLE dummy_data (
ID number NOT NULL
,test_Timestamp timestamp NOT NULL,
groups varchar2(500)
);
insert into dummy_data (id,test_TimeStamp,groups) values (1,'08-MAR-10 09.43.30.922000000','group1,group2,group3');
insert into dummy_data (id,test_TimeStamp,groups) values (2,'08-MAR-10 09.46.30.922000000','group1,group2');
insert into dummy_data (id,test_TimeStamp,groups) values (3,'08-MAR-10 09.23.30.922000000','group2,group4');
insert into dummy_data (id,test_TimeStamp,groups) values (4,'08-MAR-10 09.26.30.922000000','group4');
insert into dummy_data (id,test_TimeStamp,groups) values (5,'08-MAR-10 09.13.30.922000000','group3,group4');
insert into dummy_data (id,test_TimeStamp,groups) values (6,'08-MAR-10 09.12.30.922000000','group3');
commit;

I need to group the group names based on 10 minute interval of time stamp . In other words, this is the result I am looking for
08-MAR-10 09.40.00 - group1 - 2
08-MAR-10 09.40.00 - group2 - 2
08-MAR-10 09.40.00 - group3 - 1
08-MAR-10 09.20.00 - group2 - 1
08-MAR-10 09.20.00 - group4 - 2
08-MAR-10 09.10.00 - group4 - 1
08-MAR-10 09.10.00 - group3 - 2

Anyway to parse using regular expression ?

Thanks
_Pete
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 15 2010
Added on Nov 16 2010
20 comments
2,267 views