Link to home
Create AccountLog in
Avatar of LuckyLucks
LuckyLucks

asked on

Pivoting oracle table

Hi

I have a table with 3 columns of data and would like to pivot it as the result shown below, please help me with the pivot or other syntax.

CREATE TABLE pivot_test (
  id            NUMBER,
  customer_id   NUMBER,
  COURSE  VARCHAR2(5),
  GRADE   VARCHAR(25)
);

INSERT INTO pivot_test VALUES (1, 1, 'A', 'GOOD');
INSERT INTO pivot_test VALUES (2, 1, 'B', 'GOOD');
INSERT INTO pivot_test VALUES (3, 1, 'C', 'GOOD');
INSERT INTO pivot_test VALUES (4, 2, 'A', 'BAD');
INSERT INTO pivot_test VALUES (5, 2, 'C', 'POOR');
INSERT INTO pivot_test VALUES (6, 3, 'A', 'GOOD');
INSERT INTO pivot_test VALUES (7, 3, 'B', 'POOR');
INSERT INTO pivot_test VALUES (8, 3, 'C', 'GOOD');
INSERT INTO pivot_test VALUES (9, 4, 'A', 'POOR');

select * from pivot_test;



 
                                            A                          B                                  C
 
 CUSTOMERID   Good Bad Poor     Good Bad Poor         Good Bad Poor
 
 1                              1                            1                                    1
 2                                      1                                                                              1
 3                               1                                            1                    1
4                                              1
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Not sure why you have this in the MongoDB topic area.
I've added Oracle area to attract the correct Experts.
How are you using/consuming this data? through java? php?  in a report writer?

i.e. is there some other place, except sql, where this pivot can be performed?

sql will want to combine those headings, fo example

 CUSTOMERID   A_Good A_Bad A_Poor     B_Good B_Bad B_Poor         C_Good C_Bad C_Poor
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Try this:
select * from (
	select customer_id, course from pivot_test
)
pivot (count(*) for  course in ('A','B','C'))
order by customer_id
/

Open in new window

Avatar of LuckyLucks

ASKER

Hi:  

 I am missing the GOOD BAD POOR headers. So, in your returned result :

CUSTOMER_ID      'A'      'B'      'C'
                            G B P    G B P   G B P
1                          1                1             1

etc..

2      1      0      1
3      1      1      1
4      1      0      0
OH, I misunderstood.  You want the data pivoted not only by course but also by grade.

I'm not sure I can get the column headers working like you want but they shouldn't matter.

Give me a couple of minutes.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
If you prefer the old method of pivoting before the PIVOT operator:
	select customer_id,
		count(case when course='A' and grade='GOOD' then 1 end) a_good,
		count(case when course='A' and grade='BAD' then 1 end) a_bad,
		count(case when course='A' and grade='POOR' then 1 end) a_poor,
		count(case when course='B' and grade='GOOD' then 1 end) b_good,
		count(case when course='B' and grade='BAD' then 1 end) b_bad,
		count(case when course='B' and grade='POOR' then 1 end) b_poor,
		count(case when course='C' and grade='GOOD' then 1 end) c_good,
		count(case when course='C' and grade='BAD' then 1 end) c_bad,
		count(case when course='C' and grade='POOR' then 1 end) c_poor
	from pivot_test
	group by customer_id
	order by customer_id
/

Open in new window

Thanks, but I am curious if there is a way to do this without seeming to hardcode the course values, as those change in the database, say every semester and I would not want to keep changing code. The Good , BAD , POOR is sort of static so that does not change. Both the solutions above seem to need to use the A, B, C.
No way for dynamic columns.  There are some 3rd party products that claim to be able to do it but I'be never used them.

Search the Web for Oracle dynamic columns.