Home » SQL & PL/SQL » SQL & PL/SQL » MAX(DECODE) to make value (row) as column header (Oracle 9i, Sun Solaris)
| MAX(DECODE) to make value (row) as column header [message #440554] |
Mon, 25 January 2010 11:19  |
poratips
Messages: 345
Registered: April 2005
Location: IL
|
Senior Member |
|
|
Hi,
I need to get the report for each subject teachers but some of the subject has more than one teachers but most of the subject having only one teacher.
I tried to pull the data through DECODE statements as below but but its generates more than 50 header columns for each instructor as my query contains more than
50 teachers.
whole query is unique except teacher as same subject, same session and location but only teacher might only one or more than one.
Like
Teacher1 Teacher2 Teacher3 Teacher4 Teacher5 Teacher6 .... Teacher50
I don't want to make whole big line for column header for different teacher if same Subject, same session and location.
If any Subject has more than one Teacher for same time and location then only i want to show other wise it will go instuctor1.
I don't know the possibility of contains more than two or three or four Teacher but it might be the case.
I want like:
[b]Subject session time location intructor1 intructor2
Subject1 session1 01/25/10 Loc1 john
Subject2 session2 01/25/10 Loc2 mike larry
Subject3 session3 01/25/10 Loc3 Lisa
Subject4 session4 01/25/10 Loc1 Marry [/b]
I am using following query:
select Subject,
user,
session, LOC, room#, Subject_date, start_time, end_time,
MAX(DECODE(Teacher_NAME, 'John', Teacher_NAME, '')) "Teacher1",
MAX(DECODE(Teacher_NAME, 'Mike', Teacher_NAME, '')) "Teacher2",
MAX(DECODE(Teacher_NAME, 'LArry', Teacher_NAME, '')) "Teacher3",
MAX(DECODE(Teacher_NAME, 'Lisa', Teacher_NAME, '')) "Teacher4",
MAX(DECODE(Teacher_NAME, 'MArry', Teacher_NAME, '')) "Teacher5",
...
...
MAX(DECODE(Teacher_NAME, 'Teacher50', Teacher_NAME, '')) "Teacher50",
MAX(DECODE(Teacher_NAME, 'Teacher51', Teacher_NAME, '')) "Teacher51",
MAX(DECODE(Teacher_NAME, 'Teacher52', Teacher_NAME, '')) "Teacher52",
MAX(DECODE(Teacher_NAME, 'Teacher53', Teacher_NAME, '')) "Teacher53"
from
(select
a.Subject, b.login, c.session, c.LOC, c.room#, c.Subject_date, c.start_time, c.end_time, d.Teacher_NAME
from Subject a, user b, session c, TeacherS d
where a.Subject_id = c.Subject_id
and b.id = c.created_by and d.S_ID = c.S_ID
and c.type like 'Cl%'
)
GROUP BY Subject, user, session, LOC, room#, Subject_date, start_time, end_time
Please let me know if you need more information.
Thanks for your help!
Regards,
Pora
[Updated on: Tue, 26 January 2010 01:36] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: MAX(DECODE) to make value (row) as column header [message #440567 is a reply to message #440562] |
Mon, 25 January 2010 15:08  |
poratips
Messages: 345
Registered: April 2005
Location: IL
|
Senior Member |
|
|
Micheal Sorry for this.
My intention was not to post table and insert script but just trying to know if we have something similiar solution as i learned DECODE statements for this kind of question form here and i know that you have helped lot in past and you are so kind to response.
Due to multiple table joins and i have to pull the data taht way so just i avoid that but i feel sorry that causing inconviniency.
Please see script and requirements:
create table Session_Subject (
Subject_id Varchar2(40),
user_id Varchar2(10),
session_id Varchar2(30),
LOC Varchar2(40),
roomnum Varchar2(3),
Subject_date Date,
start_time Timestamp,
end_time Timestamp,
Instructor VARCAR2(40))
Insert into Session_Subject
values ('Maths', 'John', 'session1', 'loc1', 'A12', sysdate, systimestamp, systimestamp,'Smith');
Insert into Session_Subject
values ('Maths', 'Larry', 'session1', 'loc1', 'A13', sysdate, systimestamp, systimestamp, 'Paul');
Insert into Session_Subject
values ('Science', 'Mike', 'session2', 'loc1', 'A14', sysdate, systimestamp, systimestamp, 'Frank');
Insert into Session_Subject
values ('Eco', 'Lory', 'session3', 'loc1', 'A15', sysdate, systimestamp, systimestamp, 'Julia');
Insert into Session_Subject
values ('English', 'Lisa', 'session4', 'loc1', 'A16', sysdate, systimestamp, systimestamp, 'Robert');
Insert into Session_Subject
values ('Spanish', 'Marry', 'session5', 'loc1', 'A17', sysdate, systimestamp, systimestamp, 'Rodney');
I have just combined in one table which was used in join conditio nand created table without PK for testing.
When user select subject_id then mostly having only one instructor is assigned location and time so it will shows one row for that user but some subject_id if
two instructor at the same lcoation then it pulls currently two records so i wants this two records shows as one records.
Subject_id User_id session Subject_Date location intructor1 intructor2
Maths John session1 01/25/10 Loc1 Smith Paul
Science Mike session2 01/25/10 Loc2 Frank
Eco Lory session3 01/25/10 Loc3 Julia
English Lisa Session4 01/25/10 Loc4 Robert
Spanish Marry Session5 01/25/10 Loc5 Rodney
I appolize if i missed something and sorry about for my mistakes.
Please let me know if i am missing anything.
Thank you!
|
|
|
|
| Re: MAX(DECODE) to make value (row) as column header [message #440569 is a reply to message #440567] |
Mon, 25 January 2010 16:31 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
Play with this...
SQL> set linesize 132
SQL> column subject_id format a10
SQL> column session_id format a10
SQL> column loc format a6
SQL> column lst format a30
SQL>
SQL> SELECT subject_id,
2 session_id,
3 subject_date,
4 loc,
5 LTRIM (SYS_CONNECT_BY_PATH (instructor, ', '), ', ') lst
6 FROM (SELECT Subject_id,
7 session_id,
8 subject_date,
9 loc,
10 instructor,
11 ROW_NUMBER() OVER
12 (PARTITION BY subject_id, session_id, subject_date, loc ORDER BY instructor)rn,
13 COUNT(*) OVER (PARTITION BY subject_id, session_id, subject_date, loc)cnt
14 FROM session_subject)
15 WHERE rn = cnt
16 START WITH rn = 1
17 CONNECT BY PRIOR subject_id = subject_id
18 AND PRIOR rn = rn - 1
19 ORDER BY Subject_id, session_id, subject_date;
SUBJECT_ID SESSION_ID SUBJECT_D LOC LST
---------- ---------- --------- ------ ------------------------------
Eco session3 25-JAN-10 loc1 Julia
English session4 25-JAN-10 loc1 Robert
Maths session1 25-JAN-10 loc1 Paul, Smith
Science session2 25-JAN-10 loc1 Frank
Spanish session5 25-JAN-10 loc1 Rodney
SQL>
|
|
|
|
|
|
| Re: MAX(DECODE) to make value (row) as column header [message #440603 is a reply to message #440567] |
Tue, 26 January 2010 01:22 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
SQL> col subject_id format a10
SQL> col user_id format a6
SQL> col session_id format a10
SQL> col subject_date format a8 heading subjdate
SQL> col loc format a4
SQL> col instructor1 format a11
SQL> col instructor2 format a11
SQL> col instructor3 format a11
SQL> col instructor4 format a11
SQL> col instructor5 format a11
SQL> with
2 data as (
3 select subject_id, user_id, session_id, subject_date, loc, instructor,
4 row_number() over
5 (partition by subject_id, user_id, session_id, subject_date, loc
6 order by instructor) nb
7 from session_subject
8 )
9 select subject_id, user_id, session_id, subject_date, loc,
10 max(decode(nb,1,instructor)) instructor1,
11 max(decode(nb,2,instructor)) instructor2,
12 max(decode(nb,3,instructor)) instructor3,
13 max(decode(nb,4,instructor)) instructor4,
14 max(decode(nb,5,instructor)) instructor5
15 from data
16 group by subject_id, user_id, session_id, subject_date, loc
17 order by subject_id, user_id, session_id, subject_date, loc
18 /
SUBJECT_ID USER_I SESSION_ID subjdate LOC INSTRUCTOR1 INSTRUCTOR2 INSTRUCTOR3 INSTRUCTOR4 INSTRUCTOR5
---------- ------ ---------- -------- ---- ----------- ----------- ----------- ----------- -----------
Eco Lory session3 01/26/10 loc1 Julia
English Lisa session4 01/26/10 loc1 Robert
Maths John session1 01/26/10 loc1 Smith
Maths Larry session1 01/26/10 loc1 Paul
Science Mike session2 01/26/10 loc1 Frank
Spanish Marry session5 01/26/10 loc1 Rodney
6 rows selected.
I have not the result you posted because your result is wrong for the data you provided: Larry has a Maths course with Paul not John, and all locations are loc1.
If you omit user_id as in your original post you get the result:
SQL> with
2 data as (
3 select subject_id, session_id, subject_date, loc, instructor,
4 row_number() over
5 (partition by subject_id, session_id, subject_date, loc
6 order by instructor) nb
7 from session_subject
8 )
9 select subject_id, session_id, subject_date, loc,
10 max(decode(nb,1,instructor)) instructor1,
11 max(decode(nb,2,instructor)) instructor2,
12 max(decode(nb,3,instructor)) instructor3,
13 max(decode(nb,4,instructor)) instructor4,
14 max(decode(nb,5,instructor)) instructor5
15 from data
16 group by subject_id, session_id, subject_date, loc
17 order by subject_id, session_id, subject_date, loc
SUBJECT_ID SESSION_ID subjdate LOC INSTRUCTOR1 INSTRUCTOR2 INSTRUCTOR3 INSTRUCTOR4 INSTRUCTOR5
---------- ---------- -------- ---- ----------- ----------- ----------- ----------- -----------
Eco session3 01/26/10 loc1 Julia
English session4 01/26/10 loc1 Robert
Maths session1 01/26/10 loc1 Paul Smith
Science session2 01/26/10 loc1 Frank
Spanish session5 01/26/10 loc1 Rodney
5 rows selected.
Regards
Michel
[Updated on: Tue, 26 January 2010 01:34] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Tue Dec 03 12:34:26 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
