OraFAQ Forum: SQL & PL/SQL » Oracle transpose rows into columns

Home » SQL & PL/SQL » SQL & PL/SQL » Oracle transpose rows into columns (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle transpose rows into columns [message #610812]

Mon, 24 March 2014 10:56

developer12
Messages: 88
Registered: July 2013

Member

Hi,
I have a rates table with the table structure and insert scripts as:

CREATE TABLE RATES
(
  to_currency      NUMBER                     NOT NULL,
  src                  NUMBER                     NOT NULL,
  e_date             DATE                       NOT NULL,
  from_currency      NUMBER                     NOT NULL,
  rate          NUMBER
);

SET DEFINE OFF;
Insert into RATES
   (TO_CURRENCY, SRC, E_DATE, FROM_CURRENCY, RATE)
 Values
   (61, 146, TO_DATE('09/03/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 0.473645525059);
Insert into RATES
   (TO_CURRENCY, SRC, E_DATE, FROM_CURRENCY, RATE)
 Values
   (61, 146, TO_DATE('09/04/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 0.473608881496);
Insert into RATES
   (TO_CURRENCY, SRC, E_DATE, FROM_CURRENCY, RATE)
 Values
   (61, 146, TO_DATE('09/05/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 0.473596668235);
Insert into RATES
   (TO_CURRENCY, SRC, E_DATE, FROM_CURRENCY, RATE)
 Values
   (61, 146, TO_DATE('09/06/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 0.473566137839);
COMMIT;

I want the results as in attached Rates.xls.
Can anyone help how I can form a query to achieve that?

Attachment: Rates.csv
(Size: 0.63KB, Downloaded 970 times)

Report message to a moderator

Re: Oracle transpose rows into columns [message #610820 is a reply to message #610812]

Mon, 24 March 2014 12:43

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Post INLINE what you want and explain WITH WORDS what you want.
In addition, you didn't attach Rates.xls but Rates.csv; take care of what you post. If you think you have not to put some effort to post your question why do we think to put some effort to solve it?

Report message to a moderator

Re: Oracle transpose rows into columns [message #610825 is a reply to message #610820]

Mon, 24 March 2014 16:53

arunkumarmg
Messages: 1
Registered: March 2014

Junior Member

Hi,

Can't this be done using the PARTITION BY and ROW_NUMBER() clauses ? I think this can be done using that.. Please refer here for some samples :
http://stackoverflow.com/questions/10477085/oracle-partition-by-and-row-number-keyword

Regards,
Arun

[Updated on: Tue, 25 March 2014 01:01] by Moderator

Report message to a moderator

Re: Oracle transpose rows into columns [message #610858 is a reply to message #610825]

Tue, 25 March 2014 04:35

developer12
Messages: 88
Registered: July 2013

Member

Here is what I need:

FROM_CCY TO_CCY SRC RATE1 DATE1 %_CHNG1 RATE2 DATE2 %_CHNG2 RATE3 DATE3
1 61 146 0.47364553 09/03/2013 0.000 0.47364553 09/03/2013 0.000 0.47364553 09/03/2013
1 61 146 0.47360888 09/04/2013 0.000 0.47360888 09/04/2013 0.000 0.47360888 09/04/2013
1 61 146 0.47359667 09/05/2013 0.000 0.47359667 09/05/2013 0.000 0.47359667 09/05/2013
1 61 146 0.47356614 09/06/2013 0.000 0.47356614 09/06/2013 0.000 0.47356614 09/06/2013
Sorry had to upload .csv as .xls was not permitted to be uploaded.

Report message to a moderator

Re: Oracle transpose rows into columns [message #610868 is a reply to message #610858]

Tue, 25 March 2014 05:26

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This is unreadable.
Format your output.
Tell us what are the columns. What is "%_CHNG1"? Where do the values come from?

Report message to a moderator

Re: Oracle transpose rows into columns [message #610869 is a reply to message #610868]

Tue, 25 March 2014 05:35

developer12
Messages: 88
Registered: July 2013

Member

FROM_CCY	TO_CCY	SRC		RATE1		DATE1		%_CHNG1		RATE2		DATE2		%_CHNG2		RATE3		DATE3
1				61	146		0.47364553	09/03/2013	0.000		0.47364553	09/03/2013	0.000		0.47364553	09/03/2013
1				61	146		0.47360888	09/04/2013	0.000		0.47360888	09/04/2013	0.000		0.47360888	09/04/2013
1				61	146		0.47359667	09/05/2013	0.000		0.47359667	09/05/2013	0.000		0.47359667	09/05/2013
1				61	146		0.47356614	09/06/2013	0.000		0.47356614	09/06/2013	0.000		0.47356614	09/06/2013

Here FROM_CCY is FROM_CURRENCY , TO_CCY is TO_CURRENCY, SRC is SRC, DATE1/DATE2/DATE3 is E_DATE and RATE1/RATE2/RATE3 is rate from Rates table
%_CHNG1 is ((rate1-rate2)/rate2)*100 and %_CHNG2 is ((rate2-rate3)/rate3)*100

Report message to a moderator

Re: Oracle transpose rows into columns [message #610876 is a reply to message #610869]

Tue, 25 March 2014 06:49

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This is still unreadable.
In column DATE1 you have 0.47364553; in column %_CHNG1 you have 09/03/2013, this is a total mess.
Align your columns, format them to fit one line per row.

"DATE1/DATE2/DATE3 is E_DATE": which one is DATA1, which one DATE2 and which one DATE3?
Same thing for RATE*
How initial rows spread over result columns and rows? what is the rule?
If you don't specify the result you want how could you write a query to get it?

Report message to a moderator

Re: Oracle transpose rows into columns [message #610877 is a reply to message #610812]

Tue, 25 March 2014 07:20

developer12
Messages: 88
Registered: July 2013

Member

Here is the results I am trying to obtain.
Attached-results.png.

Attachment: results.png
(Size: 66.93KB, Downloaded 643 times)

Report message to a moderator

Re: Oracle transpose rows into columns [message #610880 is a reply to message #610877]

Tue, 25 March 2014 07:42

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You didn't answer to my questions.
From your screen shot you just want to repeat 3 times the same column with a different heading, what is your problem?

[Updated on: Sun, 29 November 2015 09:41]

Report message to a moderator

Re: Oracle transpose rows into columns [message #610881 is a reply to message #610880]

Tue, 25 March 2014 08:06

developer12
Messages: 88
Registered: July 2013

Member

Yes the same columns are repeated 3 times, as they are being updated in the table at different times which are specified in another table.
But i do not need to join to those timings, just a display of these columns as the results would do.

Report message to a moderator

Re: Oracle transpose rows into columns [message #610883 is a reply to message #610881]

Tue, 25 March 2014 08:26

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So you just have to repeat the same expression:

SQL> select sysdate data1, 1 rate1, sysdate date2, 1 rate2, sysdate date3, 1 rate3 from dual;
DATA1            RATE1 DATE2            RATE2 DATE3            RATE3
----------- ---------- ----------- ---------- ----------- ----------
25-MAR-2014          1 25-MAR-2014          1 25-MAR-2014          1

Report message to a moderator

Re: Oracle transpose rows into columns [message #610935 is a reply to message #610883]

Wed, 26 March 2014 03:09

rayudu.trinadh@gmail.com
Messages: 2
Registered: March 2014

Junior Member

Last time i had attended one interview,he simply questioned me the same.
How you will arrange rows of table data in to columns?

I had replied that using PIVOT we can do that, he asked me the syntax for the same.But it works only in 11g.

Is there any alternative way to approach the same.

for example check this:

SELECT * FROM (SELECT year(invoiceDate) as [year], left(datename(month,invoicedate),3)as [month], _
InvoiceAmount as Amount FROM Invoice) as InvoiceResult

year month amount
2011 May 100
2011 Jun 200
2011 Aug 30
2011 Sep 300

SELECT *
FROM (
SELECT
year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month],
InvoiceAmount as Amount
FROM Invoice
) as s
PIVOT
(
SUM(Amount)
FOR [month] IN ( May,Jun, Aug,Sep)
)AS pivot

result:

year May Jun Aug Sep
2011 100 200 30 300

Report message to a moderator

Re: Oracle transpose rows into columns [message #610936 is a reply to message #610935]

Wed, 26 March 2014 03:13

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Is there any alternative way to approach the same.

It is a SQL FAQ, search for "PIVOT" or "max(decode".

[Edit: add wiki links]

[Updated on: Wed, 26 March 2014 03:19]

Report message to a moderator

Previous Topic:	if in field value "Delete", then don't join other table
Next Topic:	Please help - comma separated values

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 10:59:01 CDT 2024