Home » SQL & PL/SQL » SQL & PL/SQL » Extracts subsets in the same query (Oracle 11g)
Extracts subsets in the same query [message #595063] Thu, 05 September 2013 20:59 Go to next message
athensromania
Messages: 43
Registered: July 2007
Location: Valencia,Spain
Member
Hi, the point is that I have a table with two columns and I am trying to find and easy way to show grouped data.


CREATE TABLE TEST_DATA(C1 VARCHAR2(1), C2 VARCHAR2(2));

INSERT INTO TEST_DATA VALUES('1','A1');
INSERT INTO TEST_DATA VALUES('1','A2');
INSERT INTO TEST_DATA VALUES('1','A3');

INSERT INTO TEST_DATA VALUES('2','A1');
INSERT INTO TEST_DATA VALUES('2','A2');
INSERT INTO TEST_DATA VALUES('2','A4');

INSERT INTO TEST_DATA VALUES('3','A1');
INSERT INTO TEST_DATA VALUES('3','A2');
INSERT INTO TEST_DATA VALUES('3','A3');

INSERT INTO TEST_DATA VALUES('4','A1');
INSERT INTO TEST_DATA VALUES('4','A2');
INSERT INTO TEST_DATA VALUES('4','A4');

INSERT INTO TEST_DATA VALUES('5','A6');
INSERT INTO TEST_DATA VALUES('5','A7');

COMMIT;



With the sample data there are 4 groups based in c1 column:

1 : A1,A2,A3
2 : A1,A2,A4
3 : A1,A2,A3
4 : A1,A2,A4
5 : A6,A7


I'm trying to get and output like :

A1,A2,A3 : 1,3
A1,A2,A4 : 2,4


I was trying something like :

 

  select t1.c1 as gr1, t2.c1 as gr2, t1.c2
    from test_data t1,test_data t2
    where t1.c1<>t2.c1 and t1.c2=t2.c2
    and 
    (select count(*) from test_data t3 where t3.c1=t1.c1)=
      (select count(*) from test_data t4 where t4.c1=t2.c1)
    order by 1 asc, 2 asc



but I don't find the way to refilter to group the data as expected.

Any thought?

The idea is find subsets and show the set of data and values in column c1.

Thanks and Regards.
Re: Extracts subsets in the same query [message #595067 is a reply to message #595063] Fri, 06 September 2013 00:28 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
One option:

SQL> select c2s, LISTAGG(c1, ',') WITHIN GROUP (ORDER BY c2s) C1S
  2  from (select c1, LISTAGG(c2, ',') WITHIN GROUP (ORDER BY c1) C2S
  3  	   from test_data
  4  	   group by c1)
  5  group by c2s;

C2S		C1S
--------------- ---------------
A1,A2,A3	1,3
A1,A2,A4	2,4
A6,A7		5
Re: Extracts subsets in the same query [message #595068 is a reply to message #595067] Fri, 06 September 2013 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
./fa/2115/0/ nice, short, definitive.

Regards
Michel
Re: Extracts subsets in the same query [message #595069 is a reply to message #595067] Fri, 06 September 2013 00:50 Go to previous messageGo to next message
saipradyumn
Messages: 183
Registered: October 2011
Location: Hyderabad
Senior Member


SQL> 
SQL> with data as
  2   (select c1, wm_concat(c2) group_data from TEST_DATA td group by td.c1)
  3  select to_char (wm_concat(c1)) c1, to_char(group_data) c2
  4    from data
  5   group by to_char(group_data)
  6  ;
 
C1                                                                               C2
-------------------------------------------------------------------------------- 
1,3                                                                              A1,A3,A2
2,4                                                                              A1,A4,A2
5                                                                                A6,A7
 
Re: Extracts subsets in the same query [message #595070 is a reply to message #595068] Fri, 06 September 2013 00:51 Go to previous message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
wm_concat is:
1/ undocumented
2/ installed ONLY if some option is installed
3/ useless in 11g

Regards
Michel
Previous Topic: Get Total Sales Dollar Amount from two tables without cursor
Next Topic: Connect by limit issue
Goto Forum:
  


Current Time: Sun Aug 31 02:05:38 CDT 2014

Total time taken to generate the page: 0.12471 seconds