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 Go to next message
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 #440556 is a reply to message #440554] Mon, 25 January 2010 11:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As always:
Post a working Test case: create table and insert statements along with the result you want with these data.

By the way, john, mike, Lisa and Marry are weird locations.

Regards
Michel

[Updated on: Mon, 25 January 2010 11:27]

Report message to a moderator

Re: MAX(DECODE) to make value (row) as column header [message #440561 is a reply to message #440554] Mon, 25 January 2010 12:23 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Hi Micheal.
Sorry about it!
Lisa, MAry is not the location, i just pull the name for instructor.
I am checking if instructor is John mark as Instructor1 but i might be wrong.

Re: MAX(DECODE) to make value (row) as column header [message #440562 is a reply to message #440561] Mon, 25 January 2010 12:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It's was a joke just to point that despite your 300+ posts you still don't know how to post.
Please provide what I asked.

Regards
Michel

[Updated on: Mon, 25 January 2010 12:29]

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #440570 is a reply to message #440569] Mon, 25 January 2010 16:51 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thank you very much Andrew!
I will play and let you know.
Once again thanks lot for your time and valuable guidence.
Appreciate it!
Re: MAX(DECODE) to make value (row) as column header [message #440603 is a reply to message #440567] Tue, 26 January 2010 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: MAX(DECODE) to make value (row) as column header [message #440710 is a reply to message #440603] Tue, 26 January 2010 14:33 Go to previous message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thank you very much Michel for nice solution!
I will try ti apply and let you know.
Thansk once again for your help and efforts to help the community as its most valuable for any one who is stuck and looking better guidence.

Regards,
pora
Previous Topic: Date value shows 00-000-0000
Next Topic: 24 hr report from audit script
Goto Forum:
  


Current Time: Sun Dec 11 04:14:08 CST 2016

Total time taken to generate the page: 0.11270 seconds