Home » SQL & PL/SQL » SQL & PL/SQL » Rows to columns conversionand inserting records (Oracle 9i)
Rows to columns conversionand inserting records [message #356882] Mon, 03 November 2008 00:01 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,

I have a table as follows.
Release  Mod  DB  Conf FR
--------------------------
R1        1   DBH   IJ 4
R2        2   UJH   OH 5
R3        3   HYF   KO 6


I want to create one more table as
Mod  DB  Conf  R1                R2  R3  
-----------------------------------------
1    DBH  IJ   4(FR Value for R1  5   6
                 in the above 
                  table )


I want to write a query for this . So Can any one please write a query for this issue.

Thank you.
Re: Rows to columns conversionand inserting records [message #356886 is a reply to message #356882] Mon, 03 November 2008 00:18 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
You may find the sample query in this page: http://www.orafaq.com/wiki/PIVOT

Oh, I read it again and it does not make sense to me. Can you explain the result, e.g why Mod = 1, DB = DBH, Conf = IJ shall be taken? Why not Mod = 2, DB = UJH, Conf = OH?
Also what shall be the result, when the source table contains these rows:
Release  Mod  DB  Conf FR
--------------------------
R1        1   DBH   IJ 4
R2        2   UJH   OH 5
R3        3   HYF   KO 6
R1        4   XYZ   AB 7


[Edit: added second paragraph]

[Updated on: Mon, 03 November 2008 00:24]

Report message to a moderator

Re: Rows to columns conversionand inserting records [message #356888 is a reply to message #356886] Mon, 03 November 2008 00:37 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Thank you very much....
Re: Rows to columns conversionand inserting records [message #356903 is a reply to message #356886] Mon, 03 November 2008 01:21 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi,

There we have two tables.
1. Report 1 and 2. Report2

Report1 is the already existing table.
RELEASE,MODULE_NAME,DATABASE_NAME,CONFIGERATION,FIRST_RESPONSE_TIME,AVERAGE_RESPONSE_TIME
R1	M1	D1	C1	1.5	4
R2	M2	D2	C2	2.85	5
R3	M3	D3	C3	4.5	8
R4	M4	D4	C4	8.2	6
R5	M5	D5	C5	3.2	3


Report2 is the requirement.
MODULE,DATABASE_NAME,CONFIGERATION,FRT(R1),FRT(R2),FRT(R3),
FRT(R4),FRT(R5),ART(R1),ART(R2),ART(R3),ART(R4),ART(R5)
M1	D1	C1	1.5	2.85	4.5	8.2	3.2	4	5	8	6	3


Thank you,


Re: Rows to columns conversionand inserting records [message #356913 is a reply to message #356882] Mon, 03 November 2008 01:53 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Can you explain the result? E.g. why not
MODULE,DATABASE_NAME,CONFIGERATION,FRT(R1),FRT(R2),FRT(R3),
FRT(R4),FRT(R5),ART(R1),ART(R2),ART(R3),ART(R4),ART(R5)
M4	D4	C4	1.5	2.85	4.5	8.2	3.2	4	5	8	6	3

Also, is the RELEASE column in the existing table unique? If not, what shall be the result when two rows with the same RELEASE values are present?
Previous Topic: Improper whaere clause
Next Topic: date & calendar table
Goto Forum:
  


Current Time: Sun Dec 04 18:44:02 CST 2016

Total time taken to generate the page: 0.10716 seconds