Home » SQL & PL/SQL » SQL & PL/SQL » Quey help (Oracle 10.2.0.4, AIX 5.3)
Quey help [message #570859] Fri, 16 November 2012 11:59 Go to next message
sspn2010
Messages: 147
Registered: October 2008
Senior Member
Hi,

Can someone please help me with below requirement.

create table ref_tbl (col1 varchar2(10),
                      col2 varchar2(10),
                      QUEST_ID_1 varchar2(10),
                      ANS_ID_1   varchar2(10),
                      QUEST_ID_2 varchar2(10),
                      ANS_ID_2   varchar2(10)
                     );

INSERT INTO ref_tbl (col1, col2, quest_id_1, ans_id_1, quest_id_2, ans_id_2)
  values ('PMP','1234', 'Q101','A1234', 'Q102','A2345');

INSERT INTO ref_tbl (col1, col2, quest_id_1, ans_id_1, quest_id_2, ans_id_2)
  values ('STR','2345', 'Q201','A2234', '','');  
  
commit;  

create table trans_fct (cus_id number(10),
                        col1   varchar2(10),
                        col2 varchar2(10)
                       );

Insert into trans_fct (1, 'PMP','1234');
Insert into trans_fct (1, 'STR','2345');
Insert into trans_fct (2, 'PMP','1234');
Insert into trans_fct (2, 'STR','2345');

Now i need to write a query to transpose the Quest_id and ans_id columns into rows for matching records

my output should be like

CUS_ID	COL1	COL2	QUEST_ID	ANS_ID
------------------------------------------------
1	PMP	1234	Q101		A1234
1	PMP	1234	Q102		A2345
1	STR	2345	Q201		A2234
2	PMP	1234	Q101		A1234
2	PMP	1234	Q102		A2345
2	STR	2345	Q201		A2234
Re: Quey help [message #570862 is a reply to message #570859] Fri, 16 November 2012 12:38 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't think you ever feedback and thank people that helped you on yor previous "Query help" (and other) questions.
I don't think you made any effort to search for a meaningful title.
I don't think it is worth to help you further.
Some people do not deserve to be helped.
If it is too hard for you to thank people then learn to do your job by yourself and do not rely on anyone help.
This is your best option.

Regards
Michel
Re: Quey help [message #570863 is a reply to message #570862] Fri, 16 November 2012 13:03 Go to previous messageGo to next message
sspn2010
Messages: 147
Registered: October 2008
Senior Member
Sorry Michael, Don't think me wrong. I never forget the people who helped me in my life. sorry for not posting feedbacks on earlier posts. Please help me on this request.

Thanks
Srinath
Re: Quey help [message #570868 is a reply to message #570863] Fri, 16 November 2012 13:38 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No problem, as soon as you review your previous topics.

Regards
Michel
Re: Quey help [message #570875 is a reply to message #570868] Sat, 17 November 2012 00:21 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ You have to explain the relation between the 2 tables
2/ You have to explain the result, where does come eahc column?
3/ You have to provide a VALID test case:
SQL> Insert into trans_fct (1, 'PMP','1234');
Insert into trans_fct (1, 'PMP','1234')
                       *
ERROR at line 1:
ORA-00928: missing SELECT keyword

Regards
Michel

[Updated on: Sat, 17 November 2012 00:29]

Report message to a moderator

Re: Quey help [message #570876 is a reply to message #570875] Sat, 17 November 2012 00:28 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with
  2    data as (
  3      select col1, col2, 
  4             decode(line, 1, QUEST_ID_1, 2, QUEST_ID_2) quest_id,
  5             decode(line, 1, ANS_ID_1, 2, ANS_ID_2) ans_id
  6      from ref_tbl,
  7           (select level line from dual connect by level <= 2)
  8      where decode(line, 1, QUEST_ID_1, 2, QUEST_ID_2) is not null
  9    )
 10  select t.cus_id, t.col1, t.col2, d.quest_id, d.ans_id
 11  from trans_fct t, data d
 12  where d.col1 = t.col1
 13    and d.col2 = t.col2
 14  order by t.cus_id, t.col1, t.col2, d.quest_id
 15  /
    CUS_ID COL1       COL2       QUEST_ID   ANS_ID
---------- ---------- ---------- ---------- ----------
         1 PMP        1234       Q101       A1234
         1 PMP        1234       Q102       A2345
         1 STR        2345       Q201       A2234
         2 PMP        1234       Q101       A1234
         2 PMP        1234       Q102       A2345
         2 STR        2345       Q201       A2234

Regards
Michel
Re: Quey help [message #570988 is a reply to message #570876] Mon, 19 November 2012 10:53 Go to previous messageGo to next message
sspn2010
Messages: 147
Registered: October 2008
Senior Member
Thank you for the query Michael!!! It's working.

Regards
Srinath
Re: Quey help [message #571050 is a reply to message #570988] Tue, 20 November 2012 08:11 Go to previous messageGo to next message
saipradyumn
Messages: 187
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Srinath ,

One more way to achieve the same
with data as
 (select fs.col1, fs.col2, fs.quest_id_1 QUEST_ID , fs.ans_id_1 ANS_ID
    from ref_tbl fs
  union
  select fs.col1, fs.col2, fs.quest_id_2, fs.ans_id_2 ans from ref_tbl fs), 
data_1 as 
(select  tf.cus_id , d.*  from data  d,  trans_fct tf  
where tf.col1 = d.col1
and tf.col2  = d.col2 )
select  *  from data_1    where QUEST_ID is not null and    ANS_ID is not null
 order by 1,4;



Hi Michel,

Please let me know if I am wrong

Thanks
SaiPradyumn
Re: Quey help [message #571052 is a reply to message #571050] Tue, 20 November 2012 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ UNION/UNION ALL choice is a questionable point depending on OP's need
2/ Your "data" is my "data" but with 2 table scans instead of 1 and nulls in addition
3/ Your "data_1" is useless, why did you introduce it?
4/ It is bad-formatted and hard to read

This said, nice try.
As a general rule, restrict as most as you can as earlier as you can, so the IS NOT NULL should be at the first step.

Regards
Michel

[Updated on: Tue, 20 November 2012 08:47]

Report message to a moderator

Re: Quey help [message #571075 is a reply to message #571052] Wed, 21 November 2012 00:36 Go to previous messageGo to next message
saipradyumn
Messages: 187
Registered: October 2011
Location: Hyderabad
Senior Member

Hi Michel ,

Thanks for your explanation .

Thanks
SaiPradyumn
Re: Quey help [message #571138 is a reply to message #570859] Wed, 21 November 2012 07:53 Go to previous messageGo to next message
joy_division
Messages: 4515
Registered: February 2005
Location: East Coast USA
Senior Member
Can you maybe next time come up with a better title than "Query Help?" Every one of your questions has the same title. No one is ever going to be able to do a search to find a solution to a similar problem with titles like that.
Re: Quey help [message #571147 is a reply to message #571138] Wed, 21 November 2012 09:13 Go to previous message
sspn2010
Messages: 147
Registered: October 2008
Senior Member
Sure, will use different title. Thank you all for your help on this query!!!

Thanks
Srinath
Previous Topic: Merging all subpartitions in one partition
Next Topic: Clob to flat file
Goto Forum:
  


Current Time: Mon Sep 22 00:09:00 CDT 2014

Total time taken to generate the page: 0.08247 seconds