-
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?
-
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');
-
The days between 04 and 31,
you can code as above
-
Thanks very much for your reply
-
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.