CREATE TABLE temp(tablekey varchar(10),fieldname varchar(15),olddata varchar(15));
Records are :
INSERT INTO temp values('C18CVBNK89','ACTIVE','1');
INSERT INTO temp values('C18CVBNK89','ENDDATE','NULL');
INSERT INTO temp values('C18CVBNK89','ID','01Z041782NH');
INSERT INTO temp values('C18CVBNK89','STARTDATE','01-JAN-00');
INSERT INTO temp values('C18CVBNK89','TYPE_RTK','C1X111AP59');
INSERT INTO temp values('C18CVBNK89','ENDDATE','27-OCT-05');
COMMIT;
select * from temp
select tablekey , (case when fieldname=fieldname then lag(olddata) over(order by tablekey) else fieldname end )se ,
lag(olddata) over(order by tablekey) lag1 ,lead(fieldname) over(order by tablekey) le,lag(olddata) over(order by tablekey) lag1
from temp
select tablekey , (case when fieldname=fieldname then fieldname else lag(olddata) over(order by tablekey) end ) r
, lag(olddata) over(order by tablekey) se ,olddata
--- lead(fieldname) over(order by tablekey) le
from temp
SELECT a.tablekey,a.fieldname,a.olddata
FROM temp a
WHERE EXISTS (SELECT b.fieldname FROM temp b
WHERE b.tablekey = a.tablekey)
GROUP BY a.tablekey,a.fieldname,a.olddata
ORDER BY a.tablekey,a.fieldname;
/* Formatted on 2008/10/16 18:06 (Formatter Plus v4.8.8) */
SELECT tablekey, MAX (DECODE (fieldname, 'ACTIVE', olddata)) active,
MAX (DECODE (fieldname, 'ENDDATE', olddata)) enddate,
MAX (DECODE (fieldname, 'ID', olddata)) ID,
MAX (DECODE (fieldname, 'STARTDATE', olddata)) startdate,
MAX (DECODE (fieldname, 'TYPE_RTK', olddata)) type_rtk
FROM temp
GROUP BY tablekey
ORDER BY tablekey
SELECT tablekey, MAX (DECODE (rn, 1, olddata)) AS "ACTIVE",
MAX (DECODE (rn, 2, olddata)) AS "ENDDATE",
MAX (DECODE (rn, 3, olddata)) AS "ID",
MAX (DECODE (rn, 4, olddata)) AS "STARTDATE",
MAX (DECODE (rn, 5, olddata)) AS "TYPE_RTK",
MAX (DECODE (rn, 6, olddata)) AS "ENDATE_1"
FROM (SELECT temp.*, ROW_NUMBER() OVER (PARTITION BY TABLEKEY ORDER BY TABLEKEY) RN
FROM temp)
GROUP BY tablekey
SELECT job,
sum(decode(deptno,10,sal)) DEPT10,
sum(decode(deptno,20,sal)) DEPT20,
sum(decode(deptno,30,sal)) DEPT30,
sum(decode(deptno,40,sal)) DEPT40
FROM scott.emp
GROUP BY job
SELECT job,
sum(decode(deptno,10,sal)) DEPT10,
sum(decode(deptno,20,sal)) DEPT20,
sum(decode(deptno,30,sal)) DEPT30,
sum(decode(deptno,40,sal)) DEPT40
FROM scott.emp
GROUP BY job
select *
from (
select
max(decode(attrName, 'FIRST_NAME', value, null)) first_name,
max(decode(attrName, 'LAST_NAME', value, null)) last_name,
max(decode(attrName, 'DATE_OF_BIRTH', value, null))
date_of_birth
from objects, object_attributes, attributes
where attributes.attrName in ( 'FIRST_NAME',
'LAST_NAME', 'DATE_OF_BIRTH' )
and object_attributes.attrId = attributes.attrId
and object_attributes.oid = objects.oid
and objects.name = 'PERSON'
group by objects.oid
)
where last_name = 'Smith'
or date_of_birth like '%-mar-%'
Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html
Subscribe to:
Post Comments (Atom)
My Blog List
-
-
Index Usage – 21 week ago
-
-
-
-
-
-
-
-
Oracle Cloud & Third party tools3 years ago
-
-
-
Moving Sideways7 years ago
-
Upcoming Events...10 years ago
-
No comments:
Post a Comment