Need PL/SQL Function to Generate Summary Records

[adventurous1]

Hi...

I know there is a better way to do this but I dont know PL/SQL...

My table description is:

Record_Type varchar2(2)
Key_ID Number
Category Char(3)
Create_date_time Date
Hit_Num varchar2(8)
Insert_date varchar2(40)
Activity_code Varchar2(30)

The data is arranged like this:

Record Key_ID Category Create Hit_Num Insert Activity
Type Date Date Code
Time
AD 9998 SST 10/2/06 1220 1 10/2/06 PG1
AD 9998 SST 10/2/06 1221 2 10/2/06 PG2
AD 9998 SST 10/2/06 1226 3 10/2/06 APV
AD 9999 SST 10/2/06 1930 11 10/2/06 LINK
AD 9999 SST 10/2/06 1938 12 10/2/06 PG2
AD 9999 SST 10/2/06 1950 13 10/2/06 APV
AD 9999 SST 10/2/06 1950 14 10/2/06 LEAD

So, my question is, how can I generate summary records in this format:
Record Key_ID Create Date PG1 PG2 APV LINK LEAD
AD 9998 10/2/06 x x x
AD 9999 10/2/06 x x x x

Can anyone help me or teach me how to do this?

Thanks!!!

 

[oradba101]

Hi, adventurous1

This may not be perfect but may help you find the soultion

SELECT record,
key_id,
TO_CHAR(createdatetime, 'MM/DD/YY') AS Create_Date
DECODE(activity,'PG1','x',NULL) AS PG1
DECODE(activity,'PG2','x',NULL) AS PG2
DECODE(activity,'APV','x',NULL) AS APV
DECODE(activity,'LINK','x',NULL) AS LINK
DECODE(activity,'LEAD','x',NULL) AS LEAD
GROUP BY record, key_id, createdatetime, activity

Regards,



William Chadbourne
Oracle DBA

 

[adventurous1]

Bill,

Thanks for your help. When I used the decode statement, I ended up with the following:

key id create date PG1 PG2 APV LINK LEAD
9999 12/1 x
9999 12/1 x
9999 12/1 x
9999 12/1 x
9999 12/1 x

I need it to appear as a single, consolidated record. What am I doing incorrectly? Thanks!

 

[SantaMufasa]

[tt]So, my question is, how can I generate summary records in this format:
Record Key_ID Create Date PG1 PG2 APV LINK LEAD
AD 9998 10/2/06 x x x
AD 9999 10/2/06 x x x x[/tt]

Can you please confirm for me what determines whether an "x" goes on the first row or the second row?




Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[LKBrwnDBA]

I think oradba101 meant this:

Select Record,
       Key_Id,
       Create_Date,
       Max(Pg1) As Pg1,
       Max(Pg2) As Pg2,
       Max(Apv) As Apv,
       Max(Link)As Link,
       Max(Lead)As Lead
  From (
Select Record,
       Key_Id,
       To_Char(Createdatetime, 'Mm/Dd/Yy')  As Create_Date
       Decode(Activity,'PG1','X',Null)) As Pg1
       Decode(Activity,'PG2','X',Null)) As Pg2
       Decode(Activity,'APV','X',Null)) As Apv
       Decode(Activity,'LINK','X',Null))As Link
       Decode(Activity,'LEAD','X',Null))As Lead
  From Mytable)
 Group By Record, Key_Id, Create_Date;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[adventurous1]

SantaMufasa,

Basically the goal is to take row data and transpose to columns with a flag for a given date & rowid. PL/SQL probably could do this easily but not sure on how to code the procedure or function.

LKBrwnDBA,

I will try this new code and review the results. If it works, you will definitely have my thanks!

Thanks to both of you for your assistance.