MATRIX QUERY

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-%'