Click here to Skip to main content
15,066,193 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi All,

I have a table1:

name  |  sub  |  week|status
stu1  |  math |  1  |  yes
stu1  |  eng  |  1  |  
stu1  |  sci  |  1  |  no
stu1  |  math |  2  |  yes
stu1  |  eng  |  2  |  
stu1  |  sci  |  2  |  yes



I need a pivot like:

name  |  sub  |  week1 |  week2
stu1  |  math |  yes  |  yes
stu1  |  eng  |      |  
stu1  |  sci  |  no  |  yes


I need this output for both 11g and 10g (something that works for both).

What I have tried:

I had requirement of same output, only instead of 'yes' and 'no', there were numbers
Since the server has 10g, my query was roughly:

select name, sub,
sum(case when week=1 then case when sub='math' then status
                               when sub='eng' then status
                               when sub='sci' then status end end) as week1,
sum(case when week=2 then case when sub='math' then status 
                               when sub='eng' then status 
                               when sub='sci' then status end end) as week2
from(query for table1)
group by name, sub;


But here aggregate function is not working on strings 'yes' or 'no'.
Please help!!
Posted
Updated 21-Nov-17 15:03pm
v3
Comments
CHill60 20-Nov-17 11:01am
   
You don't have a column called score.
planetz 20-Nov-17 11:03am
   
yes. This query is the old one with old table. Old table is same as table1. But instead of column 'status'(varchar2) it was column 'score'(number).
planetz 20-Nov-17 11:30am
   
the sum function will not work on status which is string, so I am not able to find a way to group the columns name and sub.
Richard Deeming 22-Nov-17 14:14pm
   
What would you expect the sum of the strings "Yes" and "No" to be? "Yes", "No", "YesNo", or something else?
planetz 27-Nov-17 9:15am
   
sum is for numbers...that's why I was looking for aggregate function for string...max() worked...
planetz 21-Nov-17 21:04pm
   
I have updated the query with present table.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900