Home » Developer & Programmer » JDeveloper, Java & XML » convert some of the rows into columns query.. (Oracle 11g)
convert some of the rows into columns query.. [message #567755] Fri, 05 October 2012 01:54 Go to next message
vai1
Messages: 6
Registered: October 2012
Junior Member
CREATE TABLE CFL (q_id Number(18),per_id number(18),PERIOD VARCHAR2(15 CHAR), AMOUNT NUMBER);

INSERT INTO CFL VALUES (11, 1, 'JAN-10', 10);
INSERT INTO CFL VALUES (21, 1, 'FEB-10', 20);
INSERT INTO CFL VALUES (31, 1, 'MAR-10', 10);
and so on (12 records for a year with same quota_id, e.g. here it is 1)
INSERT INTO CFL VALUES (121, 1, 'DEC-10', 10);


INSERT INTO CFL VALUES (12, 2, 'JAN-10', 10);
INSERT INTO CFL VALUES (22, 2, 'FEB-10', 20);
INSERT INTO CFL VALUES (32, 2, 'MAR-10', 10);
and so on (12 records for a year with same per_id, e.g. here it is 1)
INSERT INTO CFL VALUES (42, 2, 'DEC-10', 10);

COMMIT;

Also this column values period is dynamic.. it can be for any year.

The select will return values like
Select per_id, PERIOD, amount from cfl
where quota_id = 1

Basically 12 rows will be the output:
per_id period amount
1 JAN-10 10
1 FEB-10 20
1 MAR-10 10

..............and so on

1 DEC-10 10

The result output should be:

Per_id JAN-10 FEB-10 MAR-10.............DEC-10
1 10 20 10................10
Re: convert some of the rows into columns query.. [message #567756 is a reply to message #567755] Fri, 05 October 2012 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> and so on (12 records for a year with same quota_id, e.g. here it is 1)
SP2-0734: unknown command beginning "and so on ..." - rest of line ignored.


Post a VALID test case.
And post the (COMPLETE) result you want for the data you give.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

[Updated on: Fri, 05 October 2012 02:09]

Report message to a moderator

Re: convert some of the rows into columns query.. [message #567760 is a reply to message #567756] Fri, 05 October 2012 02:28 Go to previous messageGo to next message
vai1
Messages: 6
Registered: October 2012
Junior Member
I am sorry about that..
I had given a sample data..


CREATE TABLE CFL (q_id Number(18),per_id number(18),PERIOD VARCHAR2(15 CHAR), AMOUNT NUMBER);

INSERT INTO CFL VALUES (11, 1, 'JAN-10', 10);
INSERT INTO CFL VALUES (21, 1, 'FEB-10', 20);
INSERT INTO CFL VALUES (31, 1, 'MAR-10', 10);
INSERT INTO CFL VALUES (12, 2, 'JAN-10', 10);
INSERT INTO CFL VALUES (22, 2, 'FEB-10', 20);
INSERT INTO CFL VALUES (32, 2, 'MAR-10', 10);

COMMIT;

Select per_id, PERIOD, amount from cfl
where per_id = 1

/*Output will be:
per_id PERIOD amount
---------------------------------
1 JAN-10 10
1 FEB-10 20
1 MAR-10 10

The expected output that we want is:
per_id JAN-10 FEB-10 MAR-10
--------------------------------------
1 10 20 10

Also, period is dynamic. The values are not fixed.
*/
Re: convert some of the rows into columns query.. [message #567764 is a reply to message #567760] Fri, 05 October 2012 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ This is still not formatted, please read the links
2/ This is a FAQ and the common answer is that it is not possible in pure SQL
3/ Do you want alternative? There have been posted here, search for (click on the link at top of the page) "pivot", user "Barbara Boehmer".

Regards
Michel
Re: convert some of the rows into columns query.. [message #567770 is a reply to message #567755] Fri, 05 October 2012 03:53 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
Please visit: http://docs.oracle.com/cd/E14072_01/server.112/e10592/functions087.htm
Re: convert some of the rows into columns query.. [message #567773 is a reply to message #567770] Fri, 05 October 2012 04:09 Go to previous messageGo to next message
vai1
Messages: 6
Registered: October 2012
Junior Member
Veeresh,
But the output that we want is to show the columns as rows. Like the date columns that are shown above.

Michel,
Looks like creating a stored procedure is the only option. The use case that we have is we need to create a VO (View Object)
and then export that to excel and in excel the results should be shown as above.
Re: convert some of the rows into columns query.. [message #568339 is a reply to message #567773] Thu, 11 October 2012 00:48 Go to previous messageGo to next message
dude4084
Messages: 222
Registered: March 2005
Location: Mux
Senior Member
This can be easily achieve by using oracle reports "matrix" format.
Re: convert some of the rows into columns query.. [message #568347 is a reply to message #568339] Thu, 11 October 2012 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know about Reports but it can't be done by someone that does not have it, of course.

Regards
Michel
Re: convert some of the rows into columns query.. [message #568359 is a reply to message #567764] Thu, 11 October 2012 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> select * from table(pkg_pivot.pivot(
  2  '(select per_id, period, amount from cfl) pivot (sum(amount) for period in (any asc))'
  3  )) order by per_id
  4  /
    PER_ID     DEC-10     FEB-10     JAN-10     MAR-10
---------- ---------- ---------- ---------- ----------
         1         10         20         10         10
         2         10         20         10         10

Re: convert some of the rows into columns query.. [message #568430 is a reply to message #568359] Thu, 11 October 2012 09:09 Go to previous messageGo to next message
vai1
Messages: 6
Registered: October 2012
Junior Member
This is not for a report. This is for a VO (Viee Object) query
Re: convert some of the rows into columns query.. [message #568440 is a reply to message #568430] Thu, 11 October 2012 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If this is a report what it is?
"This" refers to:
    PER_ID     DEC-10     FEB-10     JAN-10     MAR-10
---------- ---------- ---------- ---------- ----------
         1         10         20         10         10
         2         10         20         10         10


Regards
Michel

[Updated on: Thu, 11 October 2012 10:23]

Report message to a moderator

Re: convert some of the rows into columns query.. [message #568452 is a reply to message #568440] Thu, 11 October 2012 22:48 Go to previous messageGo to next message
vai1
Messages: 6
Registered: October 2012
Junior Member
Basically we need to create a View Object for Export to xls
and the data in xls should be displayed in this format.

[Updated on: Thu, 11 October 2012 22:50]

Report message to a moderator

Re: convert some of the rows into columns query.. [message #568453 is a reply to message #568452] Thu, 11 October 2012 22:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Oracle does not write *xls files
Re: convert some of the rows into columns query.. [message #568454 is a reply to message #568453] Thu, 11 October 2012 22:57 Go to previous messageGo to next message
vai1
Messages: 6
Registered: October 2012
Junior Member
I am using jdev and framework to write into xls. Basically, the data from the View Object or query should get retrieved
in that format so that xls will get generated in the same way.
Re: convert some of the rows into columns query.. [message #568465 is a reply to message #568454] Fri, 12 October 2012 01:12 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So this is not the correct forum.
It is SQL forum and "view object" is meaningless here.
I move the topic to the correct forum maybe you will then the appropriate answer.

Regards
Michel
Previous Topic: java.sql.SQLException: Socket read timed out
Next Topic: loadjava error
Goto Forum:
  


Current Time: Fri Mar 29 09:45:56 CDT 2024