Thanks for the question, Khurram.
Asked: April 23, 2017 - 11:19 am UTC
Last updated: April 27, 2017 - 10:22 am UTC
Version: 11g
Viewed 10K+ times! This question is 
You Asked
Could you please help me about the below scenario : Two tables, One to Many Relation.
I tried by pivot , but pivot can only be used with aggregate functions.
master table : members
mem_id mname
------ -----
1 abc
2 xyz
3 qrs
Detail Table : subscription
mem_id sub_id start_date
---------- ------- ----------
1 10 01-mar-2017
1 20 01-apr-2017
1 30 05-apr-2017
2 10 01-jan-2017
2 20 01-feb-2017
SCRIPTS:
---------
create table members
(mem_id number,
mname varchar2(20)
);
create table subscription
(mem_id number,
sub_id number,
sdate date);
select * from members;
select * from SUBSCRIPTION;
insert into members values (1,'abc');
Insert into members values (2,'xyz');
Insert into members values(3,'qrs');
insert into subscription values (1,10,'01-mar-2017');
insert into subscription values (1,20,'01-apr-2017');
insert into subscription values (1,30,'05-apr-2017');
insert into subscription values (2,10,'01-jan-2017');
insert into subscription values (2,20,'01-feb-2017');
i need built a query to get output as :
member_id subscription subscription subscription start_date1
--------- ------------- ------------ ------------- ----------
1 10 20 30 01-mar-2017
2 10 20 01-jan-2017
and Connor said...
Whether you use PIVOT or the DECODE as below, you need know *in advance* what subscriptions you will have.
SQL>
SQL> create table members
2 (mem_id number,
3 mname varchar2(20)
4 );
Table created.
SQL>
SQL> create table subscription
2 (mem_id number,
3 sub_id number,
4 sdate date);
Table created.
SQL>
SQL> insert into members values (1,'abc');
1 row created.
SQL> Insert into members values (2,'xyz');
1 row created.
SQL> Insert into members values(3,'qrs');
1 row created.
SQL>
SQL> insert into subscription values (1,10,'01-mar-2017');
1 row created.
SQL> insert into subscription values (1,20,'01-apr-2017');
1 row created.
SQL> insert into subscription values (1,30,'05-apr-2017');
1 row created.
SQL> insert into subscription values (2,10,'01-jan-2017');
1 row created.
SQL> insert into subscription values (2,20,'01-feb-2017');
1 row created.
SQL>
SQL> select * from members;
MEM_ID MNAME
---------- --------------------
1 abc
2 xyz
3 qrs
3 rows selected.
SQL> select * from SUBSCRIPTION;
MEM_ID SUB_ID SDATE
---------- ---------- ---------
1 10 01-MAR-17
1 20 01-APR-17
1 30 05-APR-17
2 10 01-JAN-17
2 20 01-FEB-17
5 rows selected.
SQL>
SQL> select
2 mname,
3 mem_id,
4 min(decode(sub_id,10,10)) sub10,
5 min(decode(sub_id,20,20)) sub20,
6 min(decode(sub_id,30,30)) sub30,
7 min(sdate)
8 from (select m.mname, s.mem_id, s.sub_id, s.sdate
9 from members m,
10 subscription s
11 where m.mem_id = s.mem_id
12 )
13 group by
14 mname,
15 mem_id;
MNAME MEM_ID SUB10 SUB20 SUB30 MIN(SDATE
-------------------- ---------- ---------- ---------- ---------- ---------
xyz 2 10 20 01-JAN-17
abc 1 10 20 30 01-MAR-17
2 rows selected.
SQL>
SQL>
If you do *not* know the elements in advance, check out this neat solution from AMIS
https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment