Home » SQL & PL/SQL » SQL & PL/SQL » Query help for adding columns (merged)
Query help for adding columns (merged) [message #382029] Tue, 20 January 2009 13:38 Go to next message
vals
Messages: 4
Registered: January 2009
Junior Member
Hi,

I need a query where I should add each TableC value as an additional column.

Please suggest, I am sorry if formatting is not correct...

I have 3 tables (TableA, TableB, TableC). TableB stores TableA Id and TableC stores TableB Id
Considering Id of TableA.

Sample data
TableA
:
ID NAME TABLENAME ETYPE
23 Name1 TABLE NAMEA Etype A

TableB :
ID A_ID RTYPE RNAME
26 23 RTYPEA RNAMEA
61 23 RTYPEB RNAMEB

TableC :
ID B_ID COMPNAME CONC
83 26 Comp Name AA 1.5
46 26 Comp Name BB 2.2
101 61 Comp Name CC 4.2


Scenario 1:
AS PER ABOVE SAMPLE DATA Put each TableC value as an additional column.
For an Id in TableA(23) where TableB contains 2 records of A_ID (26, 61) and TableC contains 2 records for 26 and 1 record for 61.

Output required: Put each TABLEC value as an additional column

TableA.NAME TableA.ETYPE TableB.RTYPE TableC_1_COMPNAME TableC_1_CONC TableC_2_COMPNAME TableC_2_CONC
--------------------------------------------------------------
Name1 EtypeA RTypeA Comp Name AA 1.5 Comp Name BB 2.2 so on..
Name1 EtypeA RTypeB Comp Name CC 4.2 NULL NULL

Scenario 2: If Table C contains ONLY 1 row for each Id in TableB, output should be somewhat
Output:
TableA.NAME TableA.ETYPE TableB.RTYPE TableC_1_COMPNAME
TableC_1_CONC
value value value value value

Re: Query help for adding columns [message #382031 is a reply to message #382029] Tue, 20 January 2009 13:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Query help for adding columns [message #382032 is a reply to message #382029] Tue, 20 January 2009 13:44 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

It may be just me but the post make my head hurt as such a confusing jumble of characters.
Re: Query help for adding columns (merged) [message #382033 is a reply to message #382029] Tue, 20 January 2009 13:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And don't multipost your questions.

Regards
Michel
Re: Query help for adding columns (merged) [message #382035 is a reply to message #382029] Tue, 20 January 2009 13:52 Go to previous messageGo to next message
vals
Messages: 4
Registered: January 2009
Junior Member
Thanks I will take care.

Sorry, I didn't posted it twice, after session expired message I was asked to resubmit.

Thanks
Re: Query help for adding columns (merged) [message #382045 is a reply to message #382035] Tue, 20 January 2009 14:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So now post what is requested.

Regards
Michel
Re: Query help for adding columns (merged) [message #382051 is a reply to message #382029] Tue, 20 January 2009 14:54 Go to previous messageGo to next message
vals
Messages: 4
Registered: January 2009
Junior Member
Hi Michel,

Is my post "message #382029" not valid ?

Do I need to post again ?

Regards
Re: Query help for adding columns (merged) [message #382054 is a reply to message #382051] Tue, 20 January 2009 14:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said:
Quote:
Post a Test case: create table and insert statements along with the result you want with these data.


Regards
Michel
Re: Query help for adding columns (merged) [message #382061 is a reply to message #382029] Tue, 20 January 2009 15:50 Go to previous message
vals
Messages: 4
Registered: January 2009
Junior Member
Here is the test case, please suggest...

Note the 2 records with TABLE_B_ID 26 in TABLE_C should be combined to a single row as shown below.

a) Create Query
create table TABLE_A (ID number(19,0) not null, NAME varchar2(255 char), TABLENAME varchar2(255 char), 
ETYPE varchar2(255 char), primary key (ID));
create table TABLE_B (ID number(19,0) not null, RTYPE varchar2(255 char), 
RNAME varchar2(255 char), TABLE_A_ID number(19,0) not null, primary key (ID));
create table TABLE_C (ID number(19,0) not null, COMPNAME varchar2(255 char), 
CONC double precision, TABLE_B_ID number(19,0) not null, primary key (ID));


b) Insert query
Insert into TABLE_A (ID,NAME,TABLENAME,ETYPE) values (23,'Name 1','TABLE NAME A','Etype A');

Insert into TABLE_B (ID,RTYPE,RNAME,TABLE_A_ID) values (26,'RTYPE A','RNAME A',23);
Insert into TABLE_B (ID,RTYPE,RNAME,TABLE_A_ID) values (61,'RTYPE B','RNAME B',23);

Insert into TABLE_C (ID,COMPNAME,CONC,TABLE_B_ID) values (83,'Comp Name AA',1.5,26);
Insert into TABLE_C (ID,COMPNAME,CONC,TABLE_B_ID) values (46,'Comp Name BB',2.2,26);
Insert into TABLE_C (ID,COMPNAME,CONC,TABLE_B_ID) values (101,'Comp Name CC',4.2,61);


So, data will be like
TABLE_A			
ID		NAME		TABLENAME		ETYPE
23		Name 1		TABLE NAME A		Etype A
			
TABLE_B			
ID		TABLE_A_ID	RTYPE			RNAME
26		23		RTYPE A			RNAME A
61		23		RTYPE B			RNAME B
			
TABLE_C			
ID		TABLE_B_ID	COMPNAME		CONC
83		26		Comp Name AA		1.5
46		26		Comp Name BB		2.2
101		61		Comp Name CC		4.2


AS PER ABOVE SAMPLE DATA need to put each Table_C value as an additional column.

EXAMPLE : For an Id in TABLE_A(23) where TABLE_B contains 2 records of TABLE_A_ID (26, 61) and TABLE_C contains 2 records for 26 and 1 record for 61.

Output required: Put each TABLE_C value as an additional column
Note there can be "n" number of rows in "Table C" for "TABLE_B_ID"

TABLE_A_NAME	TABLE_A_ETYPE	TABLE_B_RTYPE	TABLE_C_1_COMPNAME	TABLE_C_1_CONC	TABLE_C_2_COMPNAME	TABLE_C_2_CONC

Name1		EtypeA		RTypeA		Comp Name AA		1.5		Comp Name BB		2.2 
Name1		EtypeA		RTypeB		Comp Name CC		4.2		[B]NULL[/B]		[B]NULL [/B]

[Updated on: Tue, 20 January 2009 23:07] by Moderator

Report message to a moderator

Previous Topic: ORA-00942 Errors (merged 3)
Next Topic: capture IF statement
Goto Forum:
  


Current Time: Thu Dec 08 04:21:45 CST 2016

Total time taken to generate the page: 0.10092 seconds