Avatar of jyothsna1803
jyothsna1803 asked on

How to display row value of each class as a column value

How to display row value of each class as a column value

ID      NAME      CLASS            HOURS      SCHOOLNAME
1      Allen      Maths            12      school1
2      Sam      Maths            12      school1
4      Pam      Maths            10      school1
3      Sam      Comp            10      school1
9      Smith      Comp            15      school1
7      Smith      Phy            20      school1
5      Pam      Phy            12      school1
6      Pam      Accounts      15      school1
8      Smith      Accounts      12      school1
10      Adams      Business      30      school1
11      Anthony      Comp            20      school2
12      Chris      Business      20      school2
13      Chris      Accounts      20      school2
14      Angelo      Maths            20      school3
15      Angelo      Accounts      15      school3
16      Robert      Accounts      15      school3
17      Robert      Maths            15      school3

                  




I wanted output like below for School1

Name      maths      Comp      Phy      Accounts      Business
Allen      12      0      0      0            0
Sam      12      10      0      0            0
Pam      10      0      12      15            0
Smith      0      15      20      12            0
Adams      0      0      0      0            30


I wanted output like below for School2

Name            Comp      Accounts      Business
Anthony            20      0            0
Chris            10      20            20


I wanted output like below for School3

Name      maths      Accounts
Angelo      20      15
Robert      15      15
how to wriite a single query, Also i may add /delete school/class anytime. Query should support mesans everything dynamically should get the data.
Thanx in advance
Oracle DatabaseSQL

Avatar of undefined
Last Comment
jyothsna1803

8/22/2022 - Mon
ASKER
jyothsna1803

Even PL/SQL code or procedure will do for me. as per many posts pivot table concept will work mainly for fixed number of columns. So even if good approach i will go with Stored procedure.
Please let me know the PL/SQl code with procedure,
Patrick Matthews

Hello jyothsna1803,

You should try using an Excel PivotTable to do this.  Excel is able to connect to an external data source
as long as you have the right credentials/permissions, and the PivotTable will dynamically figure out
how many rows/columns you need...

Regards,

Patrick
ASKER
jyothsna1803

No, i need to display the data on the ASP page. Also not possible to us excel for my case.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Naveen Kumar

Even if we come up with a pl/sql code, i think i am not sure whether that can work in ASP because we can come up with some pl/sql code which uses dbms_output.put_line(..) to write to the screen but i am not sure as to how the display of the result happens in ASP
Mark Wills

Well, it is going to be a little challenging to make it really dynamic, but let's start with :

select school, name ,sum(case when Class='Maths' then Hours ELSE 0 end) Maths
 ,sum(case when Class='Comp' then Hours ELSE 0 end) Comp
 ,sum(case when Class='Phy' then Hours ELSE 0 end) Phy
 ,sum(case when Class='Accounts' then Hours ELSE 0 end) Accounts
 ,sum(case when Class='Business' then Hours ELSE 0 end) Business
from mytable

group by school, name


and you need a where before group by ie : where school = ?????
ASKER
jyothsna1803

I don't want to hardcode the values like Comp, Phy it should come dynamically.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Mark Wills

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
jyothsna1803

Very good answer.