Solved

Using field values to generate columns - oracle query

Posted on 2016-10-04
6
64 Views
Last Modified: 2016-10-06
Using the following query, how could I take any field values from the homeroom column and use those values to create new columns based on the number of results (see examples below)?  I've used 3 homerooms in my example but in some cases there could be up to 10 per grade. Thanks!!

SELECT
grade_level as "Grade Level",
home_room,
COUNT(*) "Total Enrolled"
from students
where 
enroll_status=0 
AND schoolid IN (3)
group by home_room, grade_level

Open in new window


current results
Grade Level, HOME_ROOM, Total Enrolled
3, homeroom1, 17
3, homeroom2, 14
4, homeroom3, 15
5, homeroom4, 16
5, homeroom5, 18
5, homeroom6, 19

Open in new window


desired results
Grade Level, 1st Result, 2nd Result, 3rd Result, Total Enrolled
3, 17, 14, , 31
4, 15, , , 15
5, 16, 18, 19, 53

Open in new window

0
Comment
Question by:Basssque
  • 3
  • 2
6 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 41828711
  SELECT grade_level,
         MAX(CASE WHEN rn = 1 THEN cnt END) first_result,
         MAX(CASE WHEN rn = 2 THEN cnt END) second_result,
         MAX(CASE WHEN rn = 3 THEN cnt END) third_result,
         SUM(cnt)                         total_enrolled
    FROM (SELECT q.*, ROW_NUMBER() OVER(PARTITION BY grade_level ORDER BY home_room) rn
            FROM (  SELECT grade_level, home_room, COUNT(*) cnt
                      FROM students
                     WHERE enroll_status = 0 AND schoolid IN (3)
                  GROUP BY home_room, grade_level) q)
GROUP BY grade_level
ORDER BY grade_level;

Open in new window

0
 
LVL 68

Expert Comment

by:Qlemo
ID: 41828736
... and should you wonder: There is no way to have a dynamic number of columns. You always need to code for the max number of columns, even if you use the Pivot syntax not shown yet.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 41828739
a better version of what I posted above,
the first uses your original query this skips the extra layer of aggregation.

  SELECT grade_level,
         NULLIF(COUNT(CASE WHEN rn = 1 THEN 1 END), 0) first_result,
         NULLIF(COUNT(CASE WHEN rn = 2 THEN 1 END), 0) second_result,
         NULLIF(COUNT(CASE WHEN rn = 3 THEN 1 END), 0) third_result,
         COUNT(*)                                    total_enrolled
    FROM (SELECT s.*, DENSE_RANK() OVER(PARTITION BY grade_level ORDER BY home_room) rn
            FROM students s)
GROUP BY grade_level
ORDER BY grade_level;

Open in new window


and qlemo is correct, there isn't a way to create a dynamic list of columns using only sql
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:Basssque
ID: 41830551
sdstuber,

The first query worked
The second query didn't return any results except in the total column.
Is there a way to prevent columns from being rendered if there are no results in a column?  Thanks!!
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 41830716
>>> Is there a way to prevent columns from being rendered

No, that's another form of creating a dynamic result set which is not possible within SQL.
The reason is - a SQL statement must know the columns it is going to return when it is parsed (i.e. before it executes.)  So, trying to base the columns on the results of the query will never work using SQL alone.

The only way to do what you want is to use a reporting tool that can evaluate the result set and only display columns as needed,  or use pl/sql or some other language to pre-evaluate the results (generally by executing some variation of the query first) then constructing a specific sql query to return exactly what you want.  Again, this is not really a sql solution then.


>>> The second query didn't return any results except in the total column.

using what sample data?
It tested both queries using your sample data and they both worked.
0
 

Author Closing Comment

by:Basssque
ID: 41831971
Thank you again for your time!
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle - Create Procedure with Paramater 16 57
Converting a row into a column 2 44
Substring() and LEFT() syntax 4 21
Oracle Listener Not Starting 11 29
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now