Question about concatenating rows 1

[sstengler]

I have a reporting system, using 8i as the database. I have been asked to create a report that concatenates some of the data from multiple rows into a single row, as follows

Assume two tables with the following data:

Table_a

case  id   name     address
1     123  J. Blow   111 Daisy Ave
2     321  J. Smith  222 Magnolia


Table_b

case     absence_type   start_date  end_date
1         leave          1/1/04      2/1/04
1         STD            2/1/04      3/1/04
2         STD            1/1/04      4/1/04

The desired end result is:

Case  ID    Name       Absence Summary
1     123   J. Blow    Leave 1/1/04-2/1/04, STD 2/1/04-3/1/04
2     321   J. Smith   STD 1/1/04-4/1/04

I understand how to do this in a stored procedure, using a temp table and cursor, saving the results to a table that is used to populate the report. What I would like to know, is there any way to do this without having to have the intermediate steps?

I am constrained by the fact that our reporting system (COGNOS Impromptu) has no way for me to call a procedure - I'd like to be able to do this in a view, or some other Oracle object, if possible.

I understand that this is a long shot, and any and all information is appreciated.

Sam

 

[SantaMufasa]

Sam,

Although COGNOS Impromptu may not let you call a procedure, it certainly allows you to call functions (such as Oracle's UPPER, LOWER, SUBSTR, TO_CHAR, et cetera). Resultingly, create your own function to resolve your need.

Section 1 -- "STR_ABSENCES" function:

create or replace function Str_absences (case_in in number) return varchar2
is
	hold_string	varchar2(4000);
	prefix		varchar2(2)	:= null;
begin
	for r in       (select * from table_b
			where case_num = case_in
			order by start_date,end_date) loop
		hold_string	:= hold_string||prefix||r.absence_type||' '||
			to_char(r.start_date,'mm/dd/yy')||'-'||
			to_char(r.end_date,'mm/dd/yy');
		prefix	:= ', ';
	end loop;
	return hold_string;	
end;
/

Section 2 -- SQL invocation and results of your function:

set pagesize 35
col a heading "Case" format 999
col b heading "ID" format 999
col c heading "Name" format a8
col d heading "Absence Summary" format a50
select	 case_num a
	,case_id b
	,name c
	,str_absences(case_num) d
from table_a
order by case_id
/
Case   ID Name     Absence Summary
---- ---- -------- ----------------------------------------------
   1  123 J. Blow  leave 01/01/04-02/01/04, STD 02/01/04-03/01/04
   2  321 J. Smith STD 01/01/04-04/01/04

2 rows selected.

Let me know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 00:34 (01Apr04) UTC (aka "GMT" and "Zulu"), 17:34 (31Mar04) Mountain Time)

 

[sstengler]

Mufasa,

Wow....thanks!...this is something I didn't even think of....now, if i can just get the "adding user functions to Cognos" stuff working, I'll be able to solve two problems.

Thanks again!