Forum Stats

  • 3,851,919 Users
  • 2,264,053 Discussions
  • 7,904,904 Comments

Discussions

how to convert rows to columns

Onur Gul
Onur Gul Member Posts: 7
edited Jan 3, 2014 6:53AM in SQL & PL/SQL

Hi there,

DB version Oracle 11g.


İ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 in this form                 


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? Please help me.

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.

Tagged:

Answers

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond

    At the top of the forum, there's an announcement link. The second announcement is the FAQ. The fourth question in the FAQ is

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,483 Red Diamond
    edited Jan 2, 2014 11:05AM

    Hi,

    In Oracle 11. 1 (and up) you can do it like this:

    WITH    got_r_num  AS
    (
        SELECT  empno, ename, deptno
        ,       ROW_NUMBER () OVER ( PARTITION BY  deptno
                                     ORDER BY      ename
                                   )  AS r_num
        FROM    scott.emp
    )
    SELECT    *
    FROM      got_r_num
    PIVOT     (  MIN (empno) AS empno
               , MIN (ename) AS ename
                FOR  deptno IN ( 10
                               , 20
                               , 30
                               )
              )
    ORDER BY  r_num
    ;
     

    Since you didn't post CREATE TABLE and INSERT statements for your own table, I used the scott,emp table to illustrate.

    Output:

         R_NUM   10_EMPNO 10_ENAME     20_EMPNO 20_ENAME     30_EMPNO 30_ENAME
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
             1       7782 CLARK            7876 ADAMS            7499 ALLEN
             2       7839 KING             7902 FORD             7698 BLAKE
             3       7934 MILLER           7566 JONES            7900 JAMES
             4                             7788 SCOTT            7654 MARTIN
             5                             7369 SMITH            7844 TURNER
             6                                                   7521 WARD
     

    This is an example of a Prix-Fixe Query.

    The 1st row of output contains the 1st employee in each department (in alphabetic order of ename).

    The 2nd row of output contains the 2nd employee.

    ...

    The Nth row of output contains the Nth employee.

    The different employees on the Nth row don't necessarily have anything in common except that they are the Nth employee in their department.

    You don't need to display r_num in the output; I included it because it helps see what the qeury is doing.  (It's also simpler.)

    As you can see, not all departments need to have the same number of employees.  You don't need to know how many employees are, or possibly could be, in any department.

  • Onur Gul
    Onur Gul Member Posts: 7

    I search in google this question. I found four or five discussions in OTN Community, but I can't resolve my problem.
    a lot of forum or blog writing pivot function but I don't use sum or max or min function. I merge string columns. Sorry for my english, it's not enough for detailed explanation

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

    Hi,

    44e4386e-32a5-44eb-a5f5-d8a088ed0bd9 wrote:
    
    ... but I don't use sum or max or min function...
    

    Actually, you do.  Pivoting means taking 1 column from N different rows, and displaying that data as N columns on 1 row.  It uses GROUP BY (either explicitly, or, using the PIVOT keyword, implicitly) to combine those N rows into 1 row.  The value that you are displaying in the N columns must be the result of an aggregate function that looks at all N of the original rows.  In this example (as in many others), each output value displayed will depend on only 1 input value.  Since there is only 1 value in the group, eith MIN or MAX (it doesn;'t matter which) will return that value.

  • BluShadow
    BluShadow Member, Moderator Posts: 42,311 Red Diamond

    Thread continued over on the branched off post...

    https://community.oracle.com/thread/2617339

    Locking this thread.

This discussion has been closed.