OraFAQ Forum: JDeveloper, Java & XML » convert some of the rows into columns query..

Home » Developer & Programmer » JDeveloper, Java & XML » convert some of the rows into columns query.. (Oracle 11g)

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

convert some of the rows into columns query.. [message #567755]

Fri, 05 October 2012 01:54

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

Report message to a moderator

Re: convert some of the rows into columns query.. [message #567756 is a reply to message #567755]

Fri, 05 October 2012 02:07

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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

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.
*/

Report message to a moderator

Re: convert some of the rows into columns query.. [message #567764 is a reply to message #567760]

Fri, 05 October 2012 02:33

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: convert some of the rows into columns query.. [message #567770 is a reply to message #567755]

Fri, 05 October 2012 03:53

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

Report message to a moderator

Re: convert some of the rows into columns query.. [message #567773 is a reply to message #567770]

Fri, 05 October 2012 04:09

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.

Report message to a moderator

Re: convert some of the rows into columns query.. [message #568339 is a reply to message #567773]

Thu, 11 October 2012 00:48

dude4084
Messages: 222
Registered: March 2005
Location: Mux

Senior Member

This can be easily achieve by using oracle reports "matrix" format.

Report message to a moderator

Re: convert some of the rows into columns query.. [message #568347 is a reply to message #568339]

Thu, 11 October 2012 01:35

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: convert some of the rows into columns query.. [message #568359 is a reply to message #567764]

Thu, 11 October 2012 02:47

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: convert some of the rows into columns query.. [message #568430 is a reply to message #568359]

Thu, 11 October 2012 09:09

vai1
Messages: 6
Registered: October 2012

Junior Member

This is not for a report. This is for a VO (Viee Object) query

Report message to a moderator

Re: convert some of the rows into columns query.. [message #568440 is a reply to message #568430]

Thu, 11 October 2012 10:18

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Oracle does not write *xls files

Report message to a moderator

Re: convert some of the rows into columns query.. [message #568454 is a reply to message #568453]

Thu, 11 October 2012 22:57

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.

Report message to a moderator

Re: convert some of the rows into columns query.. [message #568465 is a reply to message #568454]

Fri, 12 October 2012 01:12

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Previous Topic:	java.sql.SQLException: Socket read timed out
Next Topic:	loadjava error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 03 16:42:51 CST 2025