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 Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/3cb982a71e7bf93609cc9b835cd6673b?s=64&d=mm&r=g) |
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 #567760 is a reply to message #567756] |
Fri, 05 October 2012 02:28 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/3cb982a71e7bf93609cc9b835cd6673b?s=64&d=mm&r=g) |
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.
*/
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Jul 01 10:56:20 CDT 2024
|