Home » SQL & PL/SQL » SQL & PL/SQL » grouping data
grouping data [message #195035] Tue, 26 September 2006 12:45 Go to next message
celio
Messages: 3
Registered: September 2006
Junior Member
Hello there!

What I want to do if possible, is a SELECT that returns something like this from the table bellow:

Semester Disc Class day start/end day start/end day start/end
------------------------------------------------------------
20062 MAT1003 33A 2 13:00/15:00 4 11:00/13:00 4 13:00/15:00
20062 MAT1003 33B 2 07:00/09:00 4 07:00/09:00


To group days and hours on same line for one discipline/class
Numbers 2 thru 5 stands for days of week, example, 2 = MON



TABLE CLASSES
Semester Discipline Class Day start end
--------------------------------------------------------
20062 MAT1003 33A 2 13:00 15:00
20062 MAT1003 33A 4 11:00 13:00
20062 MAT1003 33A 4 13:00 15:00
20062 MAT1003 33B 2 07:00 09:00
20062 MAT1003 33B 4 07:00 09:00


Hope you understand,
Thanks,
Celio


Re: grouping data [message #195047 is a reply to message #195035] Tue, 26 September 2006 14:04 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Yes, this is called a Pivot or Crosstab query. Search here on the forum and you will see plenty of examples.
Re: grouping data [message #195056 is a reply to message #195047] Tue, 26 September 2006 15:20 Go to previous messageGo to next message
celio
Messages: 3
Registered: September 2006
Junior Member
thank you very much!!!
Re: grouping data [message #195257 is a reply to message #195035] Wed, 27 September 2006 11:35 Go to previous message
celio
Messages: 3
Registered: September 2006
Junior Member
here it is, did it hehe

SELECT PERIODO,DISCIPLINA,TURMA,
MAX(DECODE(DIA,2,TO_CHAR(ENTRADA)||TO_CHAR(SAIDA), NULL)) SEGUNDA,
MAX(DECODE(DIA,3,TO_CHAR(ENTRADA)||TO_CHAR(SAIDA), NULL)) TERCA,
MAX(DECODE(DIA,4,TO_CHAR(ENTRADA)||TO_CHAR(SAIDA), NULL)) QUARTA,
MAX(DECODE(DIA,5,TO_CHAR(ENTRADA)||TO_CHAR(SAIDA), NULL)) QUINTA,
MAX(DECODE(DIA,6,TO_CHAR(ENTRADA)||TO_CHAR(SAIDA), NULL)) SEXTA,
MAX(DECODE(DIA,7,TO_CHAR(ENTRADA)||TO_CHAR(SAIDA), NULL)) SABADO
FROM (SELECT PERIODO, DISCIPLINA, TURMA, DIA, MAX(ENTRADA) ENTRADA, MAX(SAIDA) SAIDA FROM HORARIOCB GROUP BY PERIODO, DISCIPLINA, TURMA,DIA ORDER BY PERIODO, DISCIPLINA, TURMA)
GROUP BY PERIODO,DISCIPLINA, TURMA
ORDER BY PERIODO,DISCIPLINA, TURMA
Previous Topic: doubt in for cursor
Next Topic: Getting wrong result on summing
Goto Forum:
  


Current Time: Sun Dec 04 02:30:46 CST 2016

Total time taken to generate the page: 0.11123 seconds