Saturday, October 10, 2009

Horizontally data generate by QUERY

SQL>
CREATE TABLE LOOP_TABLE
(
EMPID NUMBER,
EMP_TIME DATE,
EMPSTATUS NUMBER
);


SQL> select * from loop_table;

EMPID EMP_TIME EMPSTATUS
---------- --------- ----------
1 01-JAN-08 800
1 02-JAN-08 588
1 03-JAN-08 478
1 04-JAN-08 123
1 05-JAN-08 789
2 01-JAN-08 111
2 02-JAN-08 222
2 03-JAN-08 555
2 04-JAN-08 333
2 05-JAN-08 444

10 rows selected.

SQL> select empid
2 , max(decode(emp_time, '01-JAN-08', empstatus)) "JAN 1ST"
3 , max(decode(emp_time, '02-JAN-08', empstatus)) "JAN 2ND"
4 , max(decode(emp_time, '03-JAN-08', empstatus)) "JAN 3RD"
5 , max(decode(emp_time, '04-JAN-08', empstatus)) "JAN 4TH"
6 , max(decode(emp_time, '05-JAN-08', empstatus)) "JAN 5TH"
7 from (select * from loop_table)
8 group by empid;

EMPID JAN 1ST JAN 2ND JAN 3RD JAN 4TH JAN 5TH
---------- ---------- ---------- ---------- ---------- ----------
1 800 588 478 123 789
2 111 222 555 333 444

No comments: