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
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
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
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
Try this:
select * from (
select customer_id, course from pivot_test
)
pivot (count(*) for course in ('A','B','C'))
order by customer_id
/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
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
/ASKER
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.
Search the Web for Oracle dynamic columns.
I've added Oracle area to attract the correct Experts.