Skip to Main Content
Forums

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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,157 views