Rows to Columns
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Rows to Columns

  1. #1
    Join Date
    Jun 2006
    Posts
    11

    Rows to Columns

    hi all,

    I am having the current sql like this

    SELECT
    TO_CHAR(a.APPT_dtime,'DD') APPT_DATE,
    COUNT(DISTINCT A.APPT_NO) "PER DAY"
    FROM MSS_appt_HDR A
    WHERE A.appt_DTIME BETWEEN TO_DATE('01-JAN-07 00:00:00', 'dd-mon-yy hh24:mi:ss')
    AND TO_DATE('31-JAN-07 23:59:59', 'dd-mon-yy hh24:mi:ss')
    AND A.STS != 'X'
    GROUP BY TO_CHAR(A.APPT_dtime,'DD');

    and my result for the above query is like this

    APPT_DATE PER DAY
    03 18
    04 30
    05 30
    06 8
    08 34
    09 35
    10 32
    11 32
    12 33
    13 9
    15 33
    like wise it goes unto the appt made during the month.
    29 31
    30 30
    31 32

    Now I want the date to appear as column headers and below the date the per day value should come

    1 2 3 4 5 6 7 8 9 10 29 30 31
    0 0 18 30 30 8 31 30 32

    Can anyone help me in this regard?

  2. #2
    Join Date
    Nov 2007
    Location
    China
    Posts
    22
    As below, but the sql is too long
    --------------------------------------------------------------------------
    SELECT
    sum(decode(TO_CHAR(a.APPT_dtime,'DD'),'01',count(DISTINCT A.APPT_NO),0)) "01",
    sum(decode(TO_CHAR(a.APPT_dtime,'DD'),'02',count(DISTINCT A.APPT_NO),0)) "02",
    sum(decode(TO_CHAR(a.APPT_dtime,'DD'),'03',count(DISTINCT A.APPT_NO),0)) "03",
    sum(decode(TO_CHAR(a.APPT_dtime,'DD'),'30',count(DISTINCT A.APPT_NO),0)) "30",
    FROM MSS_appt_HDR A
    WHERE A.appt_DTIME BETWEEN TO_DATE('01-JAN-07 00:00:00', 'dd-mon-yy hh24:mi:ss')
    AND TO_DATE('31-JAN-07 23:59:59', 'dd-mon-yy hh24:mi:ss')
    AND A.STS != 'X'
    GROUP BY TO_CHAR(A.APPT_dtime,'DD');
    Last edited by anyoneokay; 11-29-2007 at 02:11 AM.

  3. #3
    Join Date
    Nov 2007
    Location
    China
    Posts
    22
    The days between 04 and 31,
    you can code as above

  4. #4
    Join Date
    Jun 2006
    Posts
    11
    Thanks very much for your reply

  5. #5
    Join Date
    Nov 2007
    Location
    China
    Posts
    22
    hi,man.
    Have you notice that, there is Time Difference Between your city and my city.
    Now, it's afternoon in my city.
    but i must say good night to you.
    Donít Stay Up Too Late.
    Last edited by anyoneokay; 11-29-2007 at 02:33 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width