Home » SQL & PL/SQL » SQL & PL/SQL » CAN YOU HELP ME GET THE LOGIC (ORACLE 10G)
CAN YOU HELP ME GET THE LOGIC [message #339584] Thu, 07 August 2008 21:13 Go to next message
victory_nag
Messages: 36
Registered: June 2008
Location: CA
Member
I HAVE TABLE A. THE DATA IN TABLEA IS SHOWN AS BELOW.

TableA
ID	Qstn	Ans1	Ans2	Ans3
1	 i	Y	N	Y
1		N		N
1	 k			Y
2	 i	Y	y	Y
2	 J	N	Y	Y
2	 K			
3	 J	Y		
3	 K		N	Y


THE DATA IN TABLEA SHOULD BE LOADED(INSERTED) INTO TABLEB AS SHOWN BELOW.

TABLE B

ID	QSTN1	ANS1	ANS12	ANS13	QSTN2	ANS21	ANS22	ANS23	QSTN1	ANS31	ANS32	ANS33
1	 I	 Y	 N	 Y		 N		 N	  K			 Y
2	 I	 Y	 Y	 Y	 J	 N	 Y	 Y	  K			
3					 J	 Y			  K		 N	 Y


CAN ANYONE OF YOU HELP ME GET THE LOGIC,HOW TO DO IT.


SCRIPT TO CREATE TABLES AND DATA:
CREATE TABLE TABLEA( ID NUMBER, QSTN VARCHAR2(5), ANS1 VARCHAR2(5), ANS2 VARCHAR2(5), ANS3 VARCHAR2(5) );
INSERT INTO TABLEA VALUES ( 1,'I','Y','N','Y');
INSERT INTO TABLEA VALUES (1,'','N','','N');
INSERT INTO TABLEA VALUES (1,'K','','','Y');

INSERT INTO TABLEA VALUES (2,'I','Y','Y','Y');
INSERT INTO TABLEA VALUES (2,'J','N','Y','Y');
INSERT INTO TABLEA VALUES (2,'K','','','');

INSERT INTO TABLEA VALUES (3,'J','Y','','');
INSERT INTO TABLEA VALUES (3,'K','','N','Y');

CREATE TABLE TABLEB (ID NUMBER, QSTN1 VARCHAR2(5),ANS11 VARCHAR2(5),ANS12 VARCHAR2(5), ANS13 VARCHAR2(5),
			QSTN2 VARCHAR2(5), ANS21 VARCHAR2(5), ANS22 VARCHAR2(5), ANS23 VARCHAR2(5),
			QSTN3 VARCHAR2(5), ANS31 VARCHAR2(5), ANS32 VARCHAR2(5), ANS33 VARCHAR2(5));

THANKS.


Re: CAN YOU HELP ME GET THE LOGIC [message #339588 is a reply to message #339584] Thu, 07 August 2008 21:20 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW Posting Guidelines as stated in URL above.

What are the "business rules" which dictate the order of the data in the rows in TABLE_B?

Without some rhyme or reason, data could be arbitrarily loaded into TABLE_B in any order.
Re: CAN YOU HELP ME GET THE LOGIC [message #339597 is a reply to message #339588] Thu, 07 August 2008 21:33 Go to previous messageGo to next message
victory_nag
Messages: 36
Registered: June 2008
Location: CA
Member
iT IS ONE OF MY PART IN ETL PROCESS. THE DATA IN TABLEA IS DEFINED SUCH THAT THE "ID" REPEATS 3 TIMES TO ANSWER 3 QUESTIONS.

BUT IN TABLEB ID IS PRIMARY KEY COLUMN SO THERE SHOULD NOT BE ANY DUPLICATES OF ID. HENCE THE DATA IN TABLEB SHOULB BE AS SHOWN.
Re: CAN YOU HELP ME GET THE LOGIC [message #339605 is a reply to message #339584] Thu, 07 August 2008 22:05 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Your Caps Lock key is stuck ON & needs to be repaired/replaced.

For example in TABLEB why is J in QSTN2 & not in QSTN1, etc.

>THE DATA IN TABLEA IS DEFINED SUCH THAT THE "ID" REPEATS 3 TIMES TO ANSWER 3 QUESTIONS.

Then why does ID=3 have only 2 rows & violates the requirements!

>CAN YOU HELP ME GET THE LOGIC
You offer no logic requirement to implement

You're Own Your Own (YOYO)!

[Updated on: Thu, 07 August 2008 22:15] by Moderator

Report message to a moderator

Re: CAN YOU HELP ME GET THE LOGIC [message #339644 is a reply to message #339584] Fri, 08 August 2008 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Apart from you have to know how to order the rows for each ID, it is a simple "pivot" query.

Please don't post in UPPER case.

Regards
Michel
Re: CAN YOU HELP ME GET THE LOGIC [message #339728 is a reply to message #339584] Fri, 08 August 2008 04:34 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Please go through the link below.It may be helpful for you.Such kind of query has been asked so many times here. Search by the keyword "pivot"

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:124812348063




Regards,
Oli
Re: CAN YOU HELP ME GET THE LOGIC [message #339779 is a reply to message #339728] Fri, 08 August 2008 08:06 Go to previous message
victory_nag
Messages: 36
Registered: June 2008
Location: CA
Member
Well In ID 3 we donot have records for "i". so the row with ID
3 reflects like i have shown in table b.
Previous Topic: Event Count
Next Topic: Dynamically Change Schema w/out Dynamic SQL
Goto Forum:
  


Current Time: Thu Dec 08 19:56:36 CST 2016

Total time taken to generate the page: 0.08948 seconds