Category Archives: Oracle

= Pivoting a Result Set into Multiple Rows

mysql> select job, ename from EMP order by job;
+-----------+--------+
| job       | ename  |
+-----------+--------+
| ANALYST   | SCOTT  |
| ANALYST   | FORD   |
| CLERK     | JAMES  |
| CLERK     | ADAMS  |
| CLERK     | SMITH  |
| CLERK     | MILLER |
| MANAGER   | BLAKE  |
| MANAGER   | CLARK  |
| MANAGER   | JONES  |
| PRESIDENT | KING   |
| SALESMAN  | MARTIN |
| SALESMAN  | TURNER |
| SALESMAN  | WARD   |
| SALESMAN  | ALLEN  |
+-----------+--------+
14 rows in set (0.00 sec)

== Solution: MySQL

You would like to format the result set such that each job gets its own column:

 
mysql> select max(case when job ='clerk' then ename else null end) as clerks,
    ->   max(case when job ='analyst' then ename else null end) as analysts,
    ->   max(case when job ='manager' then ename else null end) as managers,
    ->   max(case when job ='president' then ename else null end) as presidents,
    ->   max(case when job ='salesman' then ename else null end) as salesmen
    -> from ( select e.job, e.ename,
    ->                (select count(*) from EMP d
    ->                   where e.job = d.job and e.empno < d.empno) as rnk
    ->         from EMP e
    ->       ) x
    -> group by rnk;
+--------+----------+----------+------------+----------+
| clerks | analysts | managers | presidents | salesmen |
+--------+----------+----------+------------+----------+
| MILLER | FORD     | CLARK    | KING       | TURNER   |
| JAMES  | SCOTT    | BLAKE    | NULL       | MARTIN   |
| ADAMS  | NULL     | JONES    | NULL       | WARD     |
| SMITH  | NULL     | NULL     | NULL       | ALLEN    |
+--------+----------+----------+------------+----------+
4 rows in set (0.00 sec)

mysql> select deptno, max(case when job ='clerk' then ename else null end) as clerks,
    ->   max(case when job ='analyst' then ename else null end) as analysts,
    ->   max(case when job ='manager' then ename else null end) as managers,
    ->   max(case when job ='president' then ename else null end) as presidents,
    ->   max(case when job ='salesman' then ename else null end) as salesmen
    -> from ( select e.deptno, e.job, e.ename,
    ->                (select count(*) from EMP d
    ->                   where e.job = d.job and e.empno < d.empno) as rnk
    ->         from EMP e
    ->       ) x
    -> group by deptno, rnk;
+--------+--------+----------+----------+------------+----------+
| deptno | clerks | analysts | managers | presidents | salesmen |
+--------+--------+----------+----------+------------+----------+
|     10 | MILLER | NULL     | CLARK    | KING       | NULL     |
|     20 | NULL   | FORD     | NULL     | NULL       | NULL     |
|     20 | NULL   | SCOTT    | NULL     | NULL       | NULL     |
|     20 | ADAMS  | NULL     | JONES    | NULL       | NULL     |
|     20 | SMITH  | NULL     | NULL     | NULL       | NULL     |
|     30 | NULL   | NULL     | NULL     | NULL       | TURNER   |
|     30 | JAMES  | NULL     | BLAKE    | NULL       | MARTIN   |
|     30 | NULL   | NULL     | NULL     | NULL       | WARD     |
|     30 | NULL   | NULL     | NULL     | NULL       | ALLEN    |
+--------+--------+----------+----------+------------+----------+
9 rows in set (0.00 sec)

== Solution: Oracle, SQL Server

select max(case when job ='clerk' then ename else null end) as clerks,
        max(case when job ='analyst' then ename else null end) as analysts,
        max(case when job ='manager' then ename else null end) as managers,
        max(case when job ='president' then ename else null end) as presidents,
        max(case when job ='salesman' then ename else null end) as salesmen
from ( select job, ename, 
                row_number() over(partition by job order by ename) rn
        from EMP 
      ) x
group by rn;

[SQL] Filling in Missing Years in a Range of Dates

Leave a reply

Use table T10 as a pivot table (because it has 10 rows) and the built-in function EXTRACT to
generate one row for each year in the decade of 1980. Outer join to table EMP and count how
many employees were hired each year:

Result view:

YEAR CNT
---- ----------
1980 1
1981 10
1982 2
1983 1
1984 0
1985 0
1986 0
1987 0
1988 0
1989 0

== MySQL

select y.yr, coalesce(x.cnt, 0) as cnt
from ( select min_year - mod(cast(min_year as decimal), 10) + rn as yr
       from ( select (select min(extract(year from hiredate)) from EMP) as min_year,
                 id - 1 as rn
               from T10
             ) a
      ) y
      left join
      ( select extract(year from hiredate) as yr, count(*) as cnt
         from EMP
         group by extract(year from hiredate)
      ) x
      on (y.yr = x.yr);

Result:

+------+------+
| yr   | cnt  |
+------+------+
| 1980 |    1 |
| 1981 |   10 |
| 1982 |    2 |
| 1983 |    1 |
| 1984 |    0 |
| 1985 |    0 |
| 1986 |    0 |
| 1987 |    0 |
| 1988 |    0 |
| 1989 |    0 |
+------+------+
10 rows in set (0.31 sec)

== T10 pivot table

mysql> select * from T10;
+------+
| ID   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+
10 rows in set (0.11 sec)

== EMP table

mysql> select empno,hiredate from EMP order by hiredate;
+-------+------------+
| empno | hiredate   |
+-------+------------+
|  7369 | 1980-12-17 |
|  7499 | 1981-02-20 |
|  7521 | 1981-02-22 |
|  7566 | 1981-04-02 |
|  7698 | 1981-05-01 |
|  7782 | 1981-06-09 |
|  7844 | 1981-09-08 |
|  7654 | 1981-09-28 |
|  7839 | 1981-11-17 |
|  7902 | 1981-12-03 |
|  7900 | 1981-12-03 |
|  7934 | 1982-01-23 |
|  7788 | 1982-12-09 |
|  7876 | 1983-01-12 |
+-------+------------+
14 rows in set (0.07 sec)

== Oracle

select x.yr, coalesce(cnt, 0) cnt
from ( select extract(year from min(hiredate) over()) - 
              mod(extract(year from min(hiredate) over()), 10) + rownum -1 yr
       from EMP
       where rownum <= 10
     ) x
     LEFT JOIN
     ( select to_number(to_char(hiredate, 'YYYY')) yr, count(*) cnt
       from EMP
       group by to_number(to_char(hiredate, 'YYYY'))
     ) y
     ON ( x.yr = y.yr)
order by x.yr;

[Oracle] Creating calendar

7 Replies

with x
as (
select *
from (
select to_char(trunc(sysdate,’mm’)+level-1,’w') wk,
to_char(trunc(sysdate,’mm’)+level-1,’dd’) dm,
to_number(to_char(trunc(sysdate,’mm’)+level-1,’d')) dw,
to_char(trunc(sysdate,’mm’)+level-1,’mm’) curr_mth,
to_char(sysdate,’mm’) mth
from dual
connect by level <= 31
)
where curr_mth = mth
)
select
max(case dw when 1 then dm end) Su,
max(case dw when 2 then dm end) Mo,
max(case dw when 3 then dm end) Tu,
max(case dw when 4 then dm end) We,
max(case dw when 5 then dm end) Th,
max(case dw when 6 then dm end) Fr,
max(case dw when 7 then dm end) Sa
from x
group by wk
order by wk;

SQL>

SU MO TU WE TH FR SA
-- -- -- -- -- -- --
01 02 03 04 05 06 07
08 09 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31

to_char(datetime, format)
Oracle date format

With the functions to_char and to_date, a date format can be used. Example:
select to_char(sysdate,’DD/MM/YYYY HH24:MI:SS’) from dual;
will return something like: 24/03/2006 14:36:43

Here is a list of all the formats that can be used:

Format mask Description
----------- -------------------------------
CC Century
SCC Century BC prefixed with -
YYYY Year with 4 numbers
SYYY Year BC prefixed with -
IYYY ISO Year with 4 numbers
YY Year with 2 numbers
RR Year with 2 numbers with Y2k compatibility
YEAR Year in characters
SYEAR Year in characters, BC prefixed with -
BC BC/AD Indicator *
Q Quarter in numbers (1,2,3,4)
MM Month of year 01, 02...12
MONTH Month in characters (i.e. January)
MON JAN, FEB
WW Weeknumber (i.e. 1)
W Weeknumber of the month (i.e. 5)
IW Weeknumber of the year in ISO standard.
DDD Day of year in numbers (i.e. 365)
DD Day of the month in numbers (i.e. 28)
D Day of week in numbers(i.e. 7)
DAY Day of the week in characters (i.e. Monday)
FMDAY Day of the week in characters (i.e. Monday)
DY Day of the week in short character description (i.e. SUN)
J Julian Day (number of days since January 1 4713 BC, where January 1 4713 BC is 1 in Oracle)
HH Hournumber of the day (1-12)
HH12 Hournumber of the day (1-12)
HH24 Hournumber of the day with 24Hours notation (0-23)
AM AM or PM
PM AM or PM
MI Number of minutes (i.e. 59)
SS Number of seconds (i.e. 59)
SSSSS Number of seconds this day.
DS Short date format. Depends on NLS-settings. Use only with timestamp.
DL Long date format. Depends on NLS-settings. Use only with timestamp.
E Abbreviated era name. Valid only for calendars: Japanese Imperial, ROC Official and Thai Buddha.. (Input-only)
EE The full era name
FF The fractional seconds. Use with timestamp.
FF1..FF9 The fractional seconds. Use with timestamp. The digit controls the number of decimal digits used for fractional seconds.
FM Fill Mode: suppresses blianks in output from conversion
FX Format Exact: requires exact pattern matching between data and format model.
IYY or IY or I the last 3,2,1 digits of the ISO standard year. Output only
RM The Roman numeral representation of the month (I .. XII)
RR The last 2 digits of the year.
RRRR The last 2 digits of the year when used for output. Accepts fout-digit years when used for input.
SCC Century. BC dates are prefixed with a minus.
CC Century
SP Spelled format. Can appear of the end of a number element. The result is always in english. For example month 10 in format MMSP returns "ten"
SPTH Spelled and ordinal format; 1 results in first.
TH Converts a number to it's ordinal format. For example 1 becoms 1st.
TS Short time format. Depends on NLS-settings. Use only with timestamp.
TZD Abbreviated time zone name. ie PST.
TZH Time zone hour displacement.
TZM Time zone minute displacement.
TZR Time zone region
X Local radix character. In america this is a period (.)

Jinny's Blog

Developer Notes

Category Archives: Oracle

[Oracle] Suppressing Repeating Values from a Result Set

[MySQL] Pivoting a Result Set into Multiple Rows

[SQL] Filling in Missing Years in a Range of Dates

[Oracle] Creating calendar