Solved

Using field values to generate columns - oracle query

Posted on 2016-10-04
46 Views
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
home_room,
COUNT(*) "Total Enrolled"
from students
where
enroll_status=0
AND schoolid IN (3)
``````

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
``````

desired results
``````Grade Level, 1st Result, 2nd Result, 3rd Result, Total Enrolled
3, 17, 14, , 31
4, 15, , , 15
5, 16, 18, 19, 53
``````
0
Question by:Basssque
• 3
• 2

LVL 73

Accepted Solution

sdstuber earned 500 total points
``````  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)
``````
0

LVL 68

Expert Comment

... 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

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)
``````

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

Author Comment

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

>>> 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

Thank you again for your time!
0

Featured Post

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.