Forum Stats

  • 3,855,322 Users
  • 2,264,499 Discussions
  • 7,905,968 Comments

Discussions

how to convert rows to columns

Onur Gul
Onur Gul Member Posts: 7
edited Feb 28, 2014 9:54AM in SQL & PL/SQL

Hi,


I can't resolve my problem. its very important for me.


I add my CREATE TABLE and INSERT statements.

,İt's my query:

select TO_CHAR(R_TARIHI, 'DAY'), TO_CHAR(T.BASLAMA,'HH24:MI') AS SAAT,(h.adi || ' ' ||h.soyadi) as HASTA

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) order by t.baslama

results table:


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


can I convert my results table like this?

CUMAPAZARTESISALI  PERSEMBE
13:30 ORHAN SAVAS13:00 SEYHAN UNVER09:45 SEYMA DURLANIK08:30 ZUHRE YEL
14:00 FATMA ETA13:30 SELMA CALISKAN10:00 HASAN GOC08:48 AYSEL POLAT
14:30 ISMAHAN YALDIZ17:45 ESMA COMERT13:00 TURKAN BICAK09:00 AHMET OZGUNGOR
14:30 ISMAHAN YALDIZ09:12 TELEFON RANDEVUSU

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;

please someone help me. Thanks.

Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,510 Red Diamond
    edited Jan 3, 2014 7:48AM Answer ✓

    Hi,

    Thanks for posting the sampled data.

    Don't forget to post the exact results you want from that data.

    Are these the results you want?

    R_NUM FRI_S FRI_H          MON_S MON_H          THU_S THU_H
    ----- ----- -------------- ----- -------------- ----- --------------
        1 08:54 SEYMA DURLANIK 13:00 SEYHAN UNVER   08:30 ZUHRE YEL
        2 10:00 SENGUL AKBAS   13:30 SELMA CALISKAN 08:48 AYSEL POLAT
        3 13:30 ORHAN SAVAS    17:45 ESMA COMERT
        4 14:00 FATMA ETA
    

    (I only included 3 days of the week, just to save space.)

    Here's one way to get them:

    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
    -- AND     TO_CHAR (R_TARIHI, 'DY')  NOT IN ('CMT','PAZ')
    )
    SELECT    *
    FROM      got_r_num
    PIVOT     ( MIN (SAAT)    AS s
              , MIN (HASTA)   AS h
              FOR  gun IN ( 'FRI'  AS fri
    --                    , 'SAT'  AS sat
                          , 'MON'  AS mon
                          , 'THU'  AS thu
                          )
              )
    ORDER BY  r_num
    ;
    

    I used the English names for the days, because this site thinks that the abbreviation for "Cuma" is a naughty word, and won't allow me to post it.

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,510 Red Diamond
    edited Jan 3, 2014 7:48AM Answer ✓

    Hi,

    Thanks for posting the sampled data.

    Don't forget to post the exact results you want from that data.

    Are these the results you want?

    R_NUM FRI_S FRI_H          MON_S MON_H          THU_S THU_H
    ----- ----- -------------- ----- -------------- ----- --------------
        1 08:54 SEYMA DURLANIK 13:00 SEYHAN UNVER   08:30 ZUHRE YEL
        2 10:00 SENGUL AKBAS   13:30 SELMA CALISKAN 08:48 AYSEL POLAT
        3 13:30 ORHAN SAVAS    17:45 ESMA COMERT
        4 14:00 FATMA ETA
    

    (I only included 3 days of the week, just to save space.)

    Here's one way to get them:

    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
    -- AND     TO_CHAR (R_TARIHI, 'DY')  NOT IN ('CMT','PAZ')
    )
    SELECT    *
    FROM      got_r_num
    PIVOT     ( MIN (SAAT)    AS s
              , MIN (HASTA)   AS h
              FOR  gun IN ( 'FRI'  AS fri
    --                    , 'SAT'  AS sat
                          , 'MON'  AS mon
                          , 'THU'  AS thu
                          )
              )
    ORDER BY  r_num
    ;
    

    I used the English names for the days, because this site thinks that the abbreviation for "Cuma" is a naughty word, and won't allow me to post it.

  • Onur Gul
    Onur Gul Member Posts: 7
    edited Jan 3, 2014 7:55AM

    thank you for your attention. I get ora-00933 in,

    WITH    got_r_num  AS

    (

        SELECT  TO_CHAR (t.r_tarihi, 'DAY')      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, 'DAY')

                                     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 ((here))    ( MIN (SAAT)    AS s

              , MIN (HASTA)   AS h

              FOR  gun IN ( 'CUMA'  AS fri

                          , 'PAZARTESI'  AS mon

                          , 'PERSEMBE'  AS thu

                          )

              )

    ORDER BY  r_num

    ;

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,510 Red Diamond

    Hi,

    44e4386e-32a5-44eb-a5f5-d8a088ed0bd9 wrote:
    
    Hi, 
    I want to exact results form:
    
    
    
    
    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
    
    
    
    
    thanks for your attention.
    

    I don't see how you get those results from the given sample data.  For example, I don't see 'ISMAHAN' anywhere in the data; how do you get 'ISMAHAN' in the results?

  • Onur Gul
    Onur Gul Member Posts: 7

    no I haven't got this result.

    I prepare in excel this results. this records from real table.

    I had prepared before writing statements.

  • 44e4386e-32a5-44eb-a5f5-d8a088ed0bd9 wrote:
    
    no I haven't got this result.
    I prepare in excel this results. this records from real table.
    I had prepared before writing statements.
    

    Kardeşim sen ne yapmak istiyosun söyle yardımcı olayım? Hemşerimi görmüşüm bırakmam

    Translate: I am just asking him what he exactly wants to do?

  • Onur Gul
    Onur Gul Member Posts: 7

    Thank you very much Frank.

    I try on my friends computer when it's done. He using oracle 12 c version.

    108:30AYSUN GOKO08:30HASAN KOCA08:30BEKTAS KIZAR08:30SULTAN SALMAN08:30ZULUF OZGE

    How can I merge this column

    108:30 AYSUN GOKO08:30 HASAN KOCA08:30 BEKTAS KIZAR08:30 SULTAN SALMAN08:30 ZULUF OZGE
  • İstediğin kod aşağıdaki gibidir. (Here is the code that you want)

    SELECT * FROM (
    select TRIM(TO_CHAR(R_TARIHI, 'DAY')) as gun, TO_CHAR(T.BASLAMA,'HH24:MI') AS SAAT,(h.adi || ' ' ||h.soyadi) as hasta
    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)
    ) PIVOT (
    listagg(hasta) within group(order by hasta) FOR (gun) in ('PAZARTESI', 'SALI', 'CARSAMBA', 'PERSEMBE', 'CUMA')
    );
    

    Eğer günler ingilizce ise aşağıdaki gibi olması lazım.

    SELECT * FROM (
    select TRIM(TO_CHAR(R_TARIHI, 'DAY')) as gun, TO_CHAR(T.BASLAMA,'HH24:MI') AS SAAT,(h.adi || ' ' ||h.soyadi) as hasta
    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)
    ) PIVOT (
    listagg(hasta) within group(order by hasta) FOR (gun) in ('MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY')
    );
    

    Kolay gelsin

    Regards

  • Unknown
    edited Jan 3, 2014 9:42AM
    44e4386e-32a5-44eb-a5f5-d8a088ed0bd9 wrote:
    
    kardeşim valla ne deyim allah razı olsun 
    elimde yapmam gereken bir randevu tablosu var. bunları transpoze etmem gerekiyor.
    
    sorguyu bir türlü beceremedim. sağolsun Frank çok yardımcı olmaya çalıştı. fakat ora-00933 hatası alıyorum onun yazdığı sorguyla.
    nasıl çözebilirim ?
    
    
    
    

    Bilader, Frank'in yazdığı kod doğru çalışıyor, eksik olan şey sende günler türkçe tanımlı onda ingilizce dolayısıyla hata alıyorsun. Kırmızıyla boyadığım kısımları düzelteceksin, sorun bu.

    Translate: I have just said that Frank's code is working correct. The problem is if you use SQL Developer the days are describe in Turkish. Therefore you have to change the red lines.

    WITH    got_r_num  AS

    (

        SELECT  TRIM(TO_CHAR (t.r_tarihi, 'DAY'))      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

    -- AND     TO_CHAR (R_TARIHI, 'DY')  NOT IN ('CMT','PAZ')

    )

    SELECT    *

    FROM      got_r_num

    PIVOT     ( MIN (SAAT)    AS s

              , MIN (HASTA)   AS h

              FOR  gun IN ( 'PAZARTESI'

    --                    , 'SALI'

                          , 'CARSAMBA' 

                          , 'PERSEMBE' 

                          , 'CUMA')

              )

    ORDER BY  r_num

    ;



This discussion has been closed.