OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » PIVOT query (Oracle Database 11g, Linux)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

PIVOT query [message #578352]

Wed, 27 February 2013 03:55

gaganmadhu
Messages: 3
Registered: April 2008

Junior Member

Hello Experts,

I need your help in creating Oracle SQL query to fetch the information using PIVOT option.
We are populating audit table using triggers. For every update, there will be two rows into audit table, one row with all OLD values and another with all NEW values. Also every updated is uniquely identified by Sequence No. Example for phone audit is mentioned below :

CREATE TABLE test_audit_phone 
  ( 
     emplid VARCHAR2(10), 
     seqno  NUMBER, 
     action VARCHAR2(3), 
     office NUMBER, 
     mobile NUMBER 
  );

Insert some rows into table.

INSERT INTO test_audit_phone VALUES ('100',1,'OLD',1111,9999) 
/
INSERT INTO test_audit_phone VALUES ('100',1,'NEW',2222,9999) 
/
INSERT INTO test_audit_phone VALUES ('100',2,'OLD',2222,9999) 
/
INSERT INTO test_audit_phone VALUES ('100',2,'NEW',2222,8888) 
/

Table will look like the following :

SQL> SELECT * FROM sysadm.test_audit_phone ;

EMPLID          SEQNO ACT     OFFICE     MOBILE                                 
---------- ---------- --- ---------- ----------                                 
100                 1 OLD       1111       9999                                 
100                 1 NEW       2222       9999                                 
100                 2 OLD       2222       9999                                 
100                 2 NEW       2222       8888

Now we have to present data in different format. For each field, display OLD and NEW values in column format.

EMPLID	FIELD	OLD	NEW
-----   ------  ----    -----
100	OFFICE	1111	2222
100	MOBILE	9999	8888

Challenges : 1) Make pivoting with old and new values 2) For each field we have to show old and new values
3)if old and new values are same, dont show in report.

Any help will be greatly appreciated. Thank you !!

[Updated on: Wed, 27 February 2013 04:01]

Report message to a moderator

Re: PIVOT query [message #578356 is a reply to message #578352]

Wed, 27 February 2013 04:25

delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City

Senior Member

PIVOT in SELECT

regards,
Delna

Report message to a moderator

Re: PIVOT query [message #578400 is a reply to message #578356]

Wed, 27 February 2013 09:16

Michel Cadot
Messages: 54246
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Not so simple, you have UNPIVOT + PIVOT in this question.

SQL> with 
  2    data as (
  3      select emplid, seqno, action,
  4             decode(line, 1,'OFFICE', 'MOBILE') field,
  5             decode(line, 1,office, mobile) phone
  6      from test_audit_phone,
  7           (select 1 line from dual union all select 2 from dual)
  8    )
  9  select emplid, field, 
 10         max(decode(action, 'OLD', phone)) "OLD",
 11         max(decode(action, 'NEW', phone)) "NEW"
 12  from data
 13  group by emplid, seqno, field
 14  having max(decode(action, 'OLD', phone)) != max(decode(action, 'NEW', phone)) 
 15  order by emplid, seqno, field
 16  /

EMPLID     FIELD         OLD        NEW
---------- ------ ---------- ----------
100        OFFICE       1111       2222
100        MOBILE       9999       8888

2 rows selected.

Regards
Michel

Report message to a moderator

Re: PIVOT query [message #578401 is a reply to message #578400]

Wed, 27 February 2013 09:25

Michel Cadot
Messages: 54246
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Or using 11g features:

SQL> with
  2    data as (
  3      select emplid, seqno, action, field, phone
  4      from test_audit_phone
  5           unpivot (phone for field in (office as 'OFFICE', mobile as 'MOBILE'))
  6    )
  7  select emplid, field, "OLD", "NEW"
  8  from data
  9       pivot(max(phone) for action in ('OLD' as old, 'NEW' as new))
 10  where "NEW" != "OLD"
 11  order by emplid, seqno, field
 12  /
EMPLID     FIELD         OLD        NEW
---------- ------ ---------- ----------
100        OFFICE       1111       2222
100        MOBILE       9999       8888

2 rows selected.

Regards
Michel

Report message to a moderator

Re: PIVOT query [message #578434 is a reply to message #578401]

Thu, 28 February 2013 01:15

gaganmadhu
Messages: 3
Registered: April 2008

Junior Member

Excellent Michel. Thanks for the help !!

While i was working on this yesterday, i realized i cannot use PIVOT because it requires aggreate function, which expects a numeric field inside. We have many fields with Characters. Example, in real-time data, phone number is character field. I will go ahead with your first query. It is working fine.

Adding little more complexity to the query, i am brining in two new actions "Addition" and "Deletion".

INSERT INTO test_audit_phone VALUES ('101',3,'ADD',3333,7777) 
/
INSERT INTO test_audit_phone VALUES ('100',4,'DEL',2222,8888)

table will now look like :

SQL> SELECT * FROM sysadm.test_audit_phone order by seqno,action;

EMPLID          SEQNO ACTION   OFFICE     MOBILE                                 
---------- ---------- --- ---------- ----------                                 
100                 1 NEW       2222       9999                                 
100                 1 OLD       1111       9999                                 
100                 2 NEW       2222       8888                                 
100                 2 OLD       2222       9999                                 
101                 3 ADD       3333       7777                                 
100                 4 DEL       2222       8888                                 

6 rows selected.

I have to represent data in the following format. Though ADD & DEL are added, i have to fit these into OLD and NEW only. For every addition, OLD will be always blank. For every DELETE, NEW will be always blank.

EMPLID	FIELD	OLD	NEW
-----   ------  ----    -----
100	OFFICE	1111	2222
100	MOBILE	9999	8888
101     OFFICE          3333
101     MOBILE		7777
100     OFFICE  2222         
100     MOBILE	8888

I am planning to take this ADD, DEL action rows separately into the temperory table and UPDATE rows(as you suggested) separately into same staging table. If you have any suggestion to cater all these requirements into single query, please let me know. Thanks again..!!

Report message to a moderator

Re: PIVOT query [message #578436 is a reply to message #578434]

Thu, 28 February 2013 01:28

Michel Cadot
Messages: 54246
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

it requires aggreate function, which expects a numeric field inside.

Many aggregate functions does not require a number: MAX, MIN, COUNT, DENSE_RANK...

Quote:

If you have any suggestion to cater all these requirements into single query, please let me know.

Try to modify the queries I gave for the new requirements. I didn't think about it but, at first sight, it seems possible.

Regards
Michel

[Updated on: Thu, 28 February 2013 01:30]

Report message to a moderator

Re: PIVOT query [message #578438 is a reply to message #578436]

Thu, 28 February 2013 01:56

Michel Cadot
Messages: 54246
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

For instance with the pre-11g way:

SQL> with 
  2    data as (
  3      select emplid, seqno, action,
  4             decode(line, 1,'OFFICE', 'MOBILE') field,
  5             decode(line, 1,office, mobile) phone
  6      from test_audit_phone,
  7           (select 1 line from dual union all select 2 from dual)
  8    )
  9  select emplid, field, 
 10         max(decode(action, 'OLD', phone, 'DEL', phone)) "OLD",
 11         max(decode(action, 'NEW', phone, 'ADD', phone)) "NEW"
 12  from data
 13  group by emplid, seqno, field
 14  having max(decode(action, 'OLD', phone, 'DEL', phone)) is null 
 15      or max(decode(action, 'NEW', phone, 'ADD', phone)) is null
 16      or max(decode(action, 'OLD', phone, 'DEL', phone)) 
 17         != max(decode(action, 'NEW', phone, 'ADD', phone)) 
 18  order by emplid, seqno, field
 19  /

EMPLID     FIELD         OLD        NEW
---------- ------ ---------- ----------
100        OFFICE       1111       2222
100        MOBILE       9999       8888
100        MOBILE       8888
100        OFFICE       2222
101        MOBILE                  7777
101        OFFICE                  3333

Regards
Michel

Report message to a moderator