Transpose rows into columns

TABLE EMP_B
EMP_ID/EMP_NAME

111/TEST RARO/
112/ICHIGO MUJIKO
113/NORISHIMA ICHIRO
…

Table EMP_PHONE
(One employee may have up to 3 types of phone numbers, Type 1 – home, type2 – office and type 3 - cell)

EMP_ID/TYPE/PHONE NUMBER

111/1/037378877987
111/2/048880000078
112/1/034878887888
112/2/044545645878
112/3/090945564456
114/1/456777899889

With the following query, you can get all the phone number into one row for one employee


SELECT a.emp_id,
a. emp_name,
NVL(b.phone1, ‘NA’) phone_1,
NVL(b.phone2, ‘NA’) phone_2,
NVL(b.phone3, ‘NA’) phone_3
FROM
EMP_B a,
(SELECT EMP_ID
MIN(DECODE(TYPE, 1, PHONE_NUMBER, NULL)) PHONE1,
MIN(DECODE(TYPE, 2, PHONE_NUMBER, NULL)) PHONE2,
MIN(DECODE(TYPE, 3, PHONE_NUMBER, NULL)) PHONE3
FROM EMP_PHONE
GROUP BY EMP_ID) b
WHERE a.emp_id = b.emp_id (+)

The result will be

EMP_ID EMP_NAME PHONE_1 PHONE_2 PHONE_3

111 TEST_RARO 037378877987 048880000078 NA
112 ICHIGO_MUJIKO 034878887888 044545645878 90945564456
113 NORISHIMA ICHIRO NA NA NA

Strip out the number from a string

select result
from
(
select level, replace(sys_connect_by_path(x, '/'), '/') result
from
(select rownum lvl, x from (select level lvl, substr('XYZabcd1234.34efEFGgh', level, 1) x
from dual connect by 1 = 1 and level <= length('XYZabcd1234.34efEFGgh'))
where x between '0' and '9' or x = '.')
connect by prior lvl = lvl -1
start with lvl = 1
order by level desc
)
where rownum < 2

RESULT
---------
1234.34

Count String Occurance

(length(str1) - length(replace(str1, str2)))/length(str2)
gives how many times str2 appears in str1.

e.g.

select (length('xyzabc123abcefgh') - length(replace('xyzabc123abcefgh', 'abc')))/length('abc') from dual

OCCURANCE
----------
2

split a string into multiple lines by comma

SQL> select c2 from test;

C2
----------------------------------------
a,b,c,dd,efg
h,iii


select
decode(lvl, 1, substr(c2, 1, instr(c2, ',', 1) - 1),
length(c2) - length(replace(c2, ',')) + 1, substr(c2, instr(c2, ',', 1, lvl-1)+1),
substr(c2, instr(c2, ',', 1, lvl-1) + 1, instr(c2, ',', 1, lvl) - instr(c2, ',', 1, lvl-1) -1))
what_you_need
from
(
select level lvl from (select max(length(c2) - length(replace(c2, ','))) + 1 maxlvl from test)
connect by 1 = 1 and level <= maxlvl
),
test
where lvl < length(c2) - length(replace(c2, ',')) + 2
/

WHAT_YOU_NEED
-------------
a
b
c
dd
efg
h
iii

Group by continuous date range

SQL> select * from sales order by 1;

EVENT_TIME AMT
-------------------- ----------
06-01-18 62
06-01-19 43
06-01-20 18
06-01-22 5
06-01-23 94
06-01-24 34
06-01-25 88
06-01-26 57
06-01-27 5
06-01-30 72
06-01-31 96
06-02-01 5
06-02-04 43

SQL> with x as (
2 select * from sales order by 1 asc
3 )
4 select min(event_time) Start_date, max(event_time) End_date, sum(amt) Amount
5 from x
6 group by event_time - rownum
7 order by 1
8 /

START_DA END_DATE AMOUNT
-------- -------- ----------
06-01-18 06-01-20 123
06-01-22 06-01-27 283
06-01-30 06-02-01 173
06-02-04 06-02-04 43

Generate continuous data

See, if you have a sales table like

SDATE AMT
---------- ----------
02/01/2006 34
02/02/2006 60
02/04/2006 35
02/05/2006 53
02/07/2006 61
02/08/2006 23
02/09/2006 34
02/10/2006 34

The data is not continuous but your employer needs a continuous
report over the whole data range 02/01/2006 - 02/10/2006

Here comes the solution

SQL> r
1 with dates as (
2 select trunc(sysdate, 'MM') + level - 1 sdate
3 from dual connect by level <= 10)
4 select dates.sdate, nvl(sales.amt, 0) amt
5 from dates, sales
6* where sales.sdate (+) = dates.sdate

SDATE AMT
---------- ----------
02/01/2006 34
02/02/2006 60
02/03/2006 0
02/04/2006 35
02/05/2006 53
02/06/2006 0
02/07/2006 61
02/08/2006 23
02/09/2006 34
02/10/2006 34

Loop

No doubt, most important features in any computer languages are condition (if statement) and loop.

In SQL, we have decode/case to do conditional check. To do a loop, before Oracle 9i, it was a no-go; now, you can have something like

SQL> SELECT rownum FROM (SELECT * FROM DUAL CONNECT BY LEVEL <= 10);

ROWNUM
----------
1
2
3
4
5
6
7
8
9
10

10 rows selected.

Simple? Ok, let's have some fun

SQL> select * from x;

C1 C2
---------- ----------
11111 1
22222 2
33333 3
44444 4


SQL> create table y (c1 number);

Table created.

SQL> insert into y
2 select a.c1 from x a,
3 (
4 select rownum rn from (select max(c2) maxlevel from x) a connect by 1=1 and level <=
5 maxlevel) b
6 where a.c2 >= rn
7 order by c1
8 /

10 rows created.

SQL> select * from y;

C1
----------
11111
22222
22222
33333
33333
33333
44444
44444
44444
44444