Oracle pivot query get ora-00933

Question

I want to transpose my results.

It's my result table:

CUMA        13:30   ORHAN SAVAS    
CUMA        14:00   FATMA ETA   
CUMA        14:30   ISMAHAN YALDIZ    
PAZARTESI   13:00   SEYHAN UNVER    
PAZARTESI   13:30   SELMA CALISKAN    
PAZARTESI   17:45   ESMA COMERT    
SALI        09:45   SEYMA DURLANIK    
SALI        10:00   HASAN GOC    
SALI        13:00   TURKAN BICAK    
SALI        14:30   ISMAHAN YALDIZ    
PERSEMBE    08:30   ZUHRE YEL    
PERSEMBE    08:48   AYSEL POLAT    
PERSEMBE    09:00   AHMET OZGUNGOR    
PERSEMBE    09:12   TELEFON RANDEVUSU

can I convert my results table like this?

CUMA                    PAZARTESI             SALI                  PERSEMBE
13:30 ORHAN SAVAS       13:00 SEYHAN UNVER    09:45 SEYMA DURLANIK  08:30 ZUHRE YEL
14:00 FATMA ETA         13:30 SELMA CALISKAN  10:00 HASAN GOC       08:48 AYSEL POLAT
14:30 ISMAHAN YALDIZ    17:45 ESMA COMERT     13:00 TURKAN BICAK    09:00 AHMET OZGUNGOR
                                              14:30 ISMAHAN YALDIZ  09:12 TELEFON RANDEVUSU

İt's my query:

WITH got_r_num  AS
(
    SELECT  TO_CHAR (t.r_tarihi, 'DY')      AS gun
    ,       TO_CHAR (t.baslama, 'HH24:MI')  AS saat
    ,       h.adi || ' ' || h.soyadi        AS hasta
    ,       ROW_NUMBER () OVER ( PARTITION BY  TO_CHAR (t.r_tarihi, 'DY')
                                 ORDER BY   t.baslama
                               )            AS r_num
    FROM             randevu_entegre_deneme  t
    LEFT OUTER JOIN  hasta_deneme            h  ON   h.id  = t.hasta_id
    WHERE   (r_tarihi   BETWEEN  TO_DATE ('20.5.2011', 'dd.mm.yyyy')
                        AND      TO_DATE ('20.5.2011', 'dd.mm.yyyy') + 9)
)
SELECT   *
FROM      got_r_num PIVOT ( MIN (SAAT) AS s , MIN (HASTA) AS h
          FOR  gun IN ( 'MON'  AS monday
                      , 'TUE'  AS tuesday
                      , 'WED'  AS wednesday
                      , 'THU'  AS thursday
                      , 'FRI'  AS friday
                      )
          )
ORDER BY  r_num;

It's my CREATE TABLE and INSERT statements

create table RANDEVU_ENTEGRE_DENEME
(
  hasta_id                     INTEGER,
  baslama                      DATE,
  R_TARIHI                        DATE
);

insert into RANDEVU_ENTEGRE_DENEME (hasta_id, baslama, r_tarihi)
values (39733, to_date('24-05-2011 13:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('24-05-2011', 'dd-mm-yyyy'));
insert into RANDEVU_ENTEGRE_DENEME (hasta_id, baslama, r_tarihi)
values (367216, to_date('23-05-2011 13:30:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('23-05-2011', 'dd-mm-yyyy'));
insert into RANDEVU_ENTEGRE_DENEME (hasta_id, baslama, r_tarihi)
values (522956, to_date('20-05-2011 13:30:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('20-05-2011', 'dd-mm-yyyy'));
insert into RANDEVU_ENTEGRE_DENEME (hasta_id, baslama, r_tarihi)
values (801923, to_date('23-05-2011 17:45:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('23-05-2011', 'dd-mm-yyyy'));
insert into RANDEVU_ENTEGRE_DENEME (hasta_id, baslama, r_tarihi)
values (815746, to_date('24-05-2011 09:45:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('24-05-2011', 'dd-mm-yyyy'));
insert into RANDEVU_ENTEGRE_DENEME (hasta_id, baslama, r_tarihi)
values (815746, to_date('20-05-2011 08:54:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('20-05-2011', 'dd-mm-yyyy'));
insert into RANDEVU_ENTEGRE_DENEME (hasta_id, baslama, r_tarihi)
values (842677, to_date('20-05-2011 14:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('20-05-2011', 'dd-mm-yyyy'));
insert into RANDEVU_ENTEGRE_DENEME (hasta_id, baslama, r_tarihi)
values (842677, to_date('24-05-2011 14:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('24-05-2011', 'dd-mm-yyyy'));
insert into RANDEVU_ENTEGRE_DENEME (hasta_id, baslama, r_tarihi)
values (854143, to_date('26-05-2011 08:48:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('26-05-2011', 'dd-mm-yyyy'));
insert into RANDEVU_ENTEGRE_DENEME (hasta_id, baslama, r_tarihi)
values (854559, to_date('23-05-2011 13:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('23-05-2011', 'dd-mm-yyyy'));
insert into RANDEVU_ENTEGRE_DENEME (hasta_id, baslama, r_tarihi)
values (861624, to_date('20-05-2011 10:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('20-05-2011', 'dd-mm-yyyy'));
insert into RANDEVU_ENTEGRE_DENEME (hasta_id, baslama, r_tarihi)
values (868595, to_date('26-05-2011 08:30:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('26-05-2011', 'dd-mm-yyyy'));
commit;

create table HASTA_DENEME
(
  id                  INTEGER,
  adi                 VARCHAR2(25),
  soyadi              VARCHAR2(25)
);

insert into HASTA_DENEME (id, adi, soyadi)
values (39733, 'TURKAN', 'BICAK');
insert into HASTA_DENEME (id, adi, soyadi)
values (367216, 'SELMA', 'CALISKAN');
insert into HASTA_DENEME (id, adi, soyadi)
values (522956, 'ORHAN', 'SAVAS');
insert into HASTA_DENEME (id, adi, soyadi)
values (801923, 'ESMA', 'COMERT');
insert into HASTA_DENEME (id, adi, soyadi)
values (815746, 'SEYMA', 'DURLANIK');
insert into HASTA_DENEME (id, adi, soyadi)
values (842677, 'FATMA', 'ETA');
insert into HASTA_DENEME (id, adi, soyadi)
values (854143, 'AYSEL', 'POLAT');
insert into HASTA_DENEME (id, adi, soyadi)
values (854559, 'SEYHAN', 'UNVER');
insert into HASTA_DENEME (id, adi, soyadi)
values (861624, 'SENGUL', 'AKBAS');
insert into HASTA_DENEME (id, adi, soyadi)
values (868595, 'ZUHRE', 'YEL');
commit;

I get ora-00933. How can I fix that..

I'm not getting that error on SQL Fiddle -- http://sqlfiddle.com/#!4/b4d6f/1 — Taryn, Jan 03 '14 at 14:03
First of all I cannot replicate the ORA-00933. So that is not your problem, if you feel that it is, then recheck your SQL what you actually used vs what you posted here. On you question of transpose... YES.. But Where on earth you get CUMA, PAZARTESI, SALI, PERSEMBE??? Dont cook your data (Turkish days you mentioned are not from the table. But from where you get them??). Post the originals. — Srini V, Jan 03 '14 at 14:09

score 3 · Accepted Answer · edited May 23 '17 at 10:25

The PIVOT operator was added in Oracle 11gR1. Attempting to run your code in 10gR2 gives your error:

FROM      got_r_num PIVOT ( MIN (SAAT) AS s , MIN (HASTA) AS h
                          *
ERROR at line 15:
ORA-00933: SQL command not properly ended

Prior to 11g you'll need to use a different mechanism, like this one.

I think you want something like this:

WITH got_r_num  AS
(
    SELECT  TO_CHAR (t.r_tarihi, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') AS gun
    ,       TO_CHAR (t.baslama, 'HH24:MI')  AS saat
    ,       h.adi || ' ' || h.soyadi        AS hasta
    ,       ROW_NUMBER () OVER ( PARTITION BY  TO_CHAR (t.r_tarihi, 'DY')
                                 ORDER BY   t.baslama
                               )            AS r_num
    FROM             randevu_entegre_deneme  t
    LEFT OUTER JOIN  hasta_deneme            h  ON   h.id  = t.hasta_id
    WHERE   (r_tarihi   BETWEEN  TO_DATE ('20.5.2011', 'dd.mm.yyyy')
                        AND      TO_DATE ('20.5.2011', 'dd.mm.yyyy') + 9)
)
SELECT    min(case when gun = 'MON' then SAAT ||' '|| HASTA end) as pazartesi
          , min(case when gun = 'TUE' then SAAT ||' '|| HASTA end) as sali
          , min(case when gun = 'WED' then SAAT ||' '|| HASTA end) as carsamba
          , min(case when gun = 'THU' then SAAT ||' '|| HASTA end) as persembe
          , min(case when gun = 'FRI' then SAAT ||' '|| HASTA end) as cuma
FROM      got_r_num
GROUP BY  r_num
ORDER BY  r_num;

With the table and insert statement you posted that gives:

PAZARTESI            SALI                 CARSAMBA             PERSEMBE             CUMA
-------------------- -------------------- -------------------- -------------------- --------------------
13:00 SEYHAN UNVER   09:45 SEYMA DURLANIK                      08:30 ZUHRE YEL      08:54 SEYMA DURLANIK
13:30 SELMA CALISKAN 13:00 TURKAN BICAK                        08:48 AYSEL POLAT    10:00 SENGUL AKBAS
17:45 ESMA COMERT    14:00 FATMA ETA                                                13:30 ORHAN SAVAS
                                                                                    14:00 FATMA ETA

... which doesn't quite match what you said you expected, but does line up with the data and bluefeet's SQl Fiddle using pivot.

I've used the Turkish day labels you seem to be expecting; as realspirituals pointed out it isn't entirely helpful to change things in your code when you post a question. The language difference is important here as your day comparison is assuming the DY format is in a certain language - whether you're actually using the English or Turkish abbreviations doesn't matter, the point is you're assuming the client and code are using the same language. You can avoid a potential mismatch there by forcing the DY to be in a specific language, so you know what you will be comparing against; that's what this is doing, using the optional third parameter to the to_char() function:

TO_CHAR (t.r_tarihi, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH')

Also, I've left it as only reporting five days as you didn't seem interested in weekends. It seems a little odd though that you have a date span of 9 days, since that will include some of those days multiple times, and you won't be able to tell which week a particular time is from. Your r_num should keep them in order, so you might see the times appearing to not be in the right order if you have them from both weeks, though they will be based on the date too. Presumably that's what you want though.

For the follow-up query about ordering the columns; you can do that by assigning another pseudo-column using dense rank and using that for the case instead of gun:

WITH got_r_num  AS
(
    SELECT  TO_CHAR (t.r_tarihi, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') AS gun
    ,       TO_CHAR (t.r_tarihi, 'DAY', 'NLS_DATE_LANGUAGE=TURKISH') AS d_name
    ,       DENSE_RANK () OVER ( ORDER BY TRUNC( t.r_tarihi) ) AS d_num
    ,       TO_CHAR (t.baslama, 'HH24:MI')  AS saat
    ,       h.adi || ' ' || h.soyadi        AS hasta
    ,       ROW_NUMBER () OVER ( PARTITION BY  TO_CHAR (t.r_tarihi, 'DY')
                                 ORDER BY   t.baslama
                               )            AS r_num
    FROM             randevu_entegre_deneme  t
    LEFT OUTER JOIN  hasta_deneme            h  ON   h.id  = t.hasta_id
    WHERE   (r_tarihi   BETWEEN  TO_DATE ('20.5.2011', 'dd.mm.yyyy')
                        AND      TO_DATE ('20.5.2011', 'dd.mm.yyyy') + 9)
)
SELECT    min(case when mod(d_num, 7) = 1 then SAAT ||' '|| HASTA end) as day1
          , min(case when mod(d_num, 7) = 2 then SAAT ||' '|| HASTA end) as day2
          , min(case when mod(d_num, 7) = 3 then SAAT ||' '|| HASTA end) as day3
          , min(case when mod(d_num, 7) = 4 then SAAT ||' '|| HASTA end) as day4
          , min(case when mod(d_num, 7) = 5 then SAAT ||' '|| HASTA end) as day5
          , min(case when mod(d_num, 7) = 6 then SAAT ||' '|| HASTA end) as day6
          , min(case when mod(d_num, 7) = 0 then SAAT ||' '|| HASTA end) as day7
FROM      got_r_num
GROUP BY  r_num
ORDER BY  r_num;

DAY1                 DAY2                 DAY3                 DAY4                 DAY5                 DAY6                 DAY7
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
08:54 SEYMA DURLANIK 13:00 SEYHAN UNVER   09:45 SEYMA DURLANIK 08:30 ZUHRE YEL
10:00 SENGUL AKBAS   13:30 SELMA CALISKAN 13:00 TURKAN BICAK   08:48 AYSEL POLAT
13:30 ORHAN SAVAS    17:45 ESMA COMERT    14:00 FATMA ETA
14:00 FATMA ETA

I'm not actually using gun any more but I've left it in, and added d_name as the full day name, though neither is currently selected. This is completely skipping what was the carsamba column because it has no data, which matches what your question showed. That might be what you want if you knew which column was which. Unfortunately you can't dynamically assign the column alias, so you don't know that day1 is actually cuma. If you want a gap for carsamba then you can work out the offset of each date from the start date:

    ,       TRUNC( t.r_tarihi) - TO_DATE ('20.5.2011', 'dd.mm.yyyy') + 1 AS d_num

But you still don't know which day column 1 represents. You can fake that but it depends how you're running it. If you are pulling the data from an application then you can query the d_name as well and use that. From SQL*Plus you could do this if you repeated the query and used substitution variables, which is proabbly a lot of overhead, or a partial solution is just to select them as a separate select, which isn't ideal either:

column r_num noprint
set head off
WITH got_r_num  AS
(
  ...
)
SELECT    0 as r_num
         , min(case when mod(d_num, 7) = 1 then d_name end) as day1
         ,  min(case when mod(d_num, 7) = 2 then d_name end) as day2
         ,  min(case when mod(d_num, 7) = 3 then d_name end) as day3
         ,  min(case when mod(d_num, 7) = 4 then d_name end) as day4
         ,  min(case when mod(d_num, 7) = 5 then d_name end) as day5
         ,  min(case when mod(d_num, 7) = 6 then d_name end) as day6
         ,  min(case when mod(d_num, 7) = 0 then d_name end) as day7
FROM      got_r_num
UNION ALL
SELECT    r_num
          , min(case when mod(d_num, 7) = 1 then SAAT ||' '|| HASTA end) as day1
          , min(case when mod(d_num, 7) = 2 then SAAT ||' '|| HASTA end) as day2
          , min(case when mod(d_num, 7) = 3 then SAAT ||' '|| HASTA end) as day3
          , min(case when mod(d_num, 7) = 4 then SAAT ||' '|| HASTA end) as day4
          , min(case when mod(d_num, 7) = 5 then SAAT ||' '|| HASTA end) as day5
          , min(case when mod(d_num, 7) = 6 then SAAT ||' '|| HASTA end) as day6
          , min(case when mod(d_num, 7) = 0 then SAAT ||' '|| HASTA end) as day7
FROM      got_r_num
GROUP BY  r_num
ORDER BY  r_num;

CUMA                 PAZARTESI            SALI                 PER¿EMBE
08:54 SEYMA DURLANIK 13:00 SEYHAN UNVER   09:45 SEYMA DURLANIK 08:30 ZUHRE YEL
10:00 SENGUL AKBAS   13:30 SELMA CALISKAN 13:00 TURKAN BICAK   08:48 AYSEL POLAT
13:30 ORHAN SAVAS    17:45 ESMA COMERT    14:00 FATMA ETA
14:00 FATMA ETA

Thank you very much Alex. That was exactly the answer I wanted. — s.onurgul, Jan 03 '14 at 15:32
Hi Alex. I have one more little question. Can I sort my columns sort by r_tarihi ? — s.onurgul, Jan 09 '14 at 09:13
@s.onurgul - what do you mean; if your range starts on a Friday then the first column should be `cuma`? — Alex Poole, Jan 09 '14 at 09:17
@Alex Poole Thank you for taking the time. Your answer was very helpful. Have a nice day. — s.onurgul, Jan 09 '14 at 11:04

Oracle pivot query get ora-00933

1 Answers1