Joining to the same table

[bina8]

I have a table that can contain multiple notes for a document. I want to read the table and display all the notes (from multiple lines)in one row. How can I do this?
A Doc may have zero to three notes.
Table example: Document table:
Doc# 1a
note_line 1
note "start of "

Doc# la
note_line 2
note "note"

I want to be able to extract "start of note"
Thanks in advance!!!

 

[SantaMufasa]

Bina,

I like the following solution:

Section 1 -- Table contents:

col a heading "Raw|Notes" format a20
select doc#, note_line, '['||note||']' a from bina8
/
                 Raw
DOC#   NOTE_LINE Notes
----- ---------- -----------
1a             1 [start of ]
1a             2 [note]
2a             1 [I ]
2a             2 [hope ]
2a             3 [this ]
2a             4 [does ]
2a             5 [what ]
2a             6 [you ]
2a             7 [want.]

9 rows selected.

Section 2 -- Function definition:

create or replace function string_note(doc_in varchar2) return varchar2 is
	str_hold	varchar2(4000);
begin
	for r in (select note from bina8 where doc# = doc_in order by note_line) loop
		str_hold := str_hold||r.note;
	end loop;
	return str_hold;
end;
/

Function created.

Section 3 -- Function invocation:

col b heading "Notes" format a40
select doc#,string_note(doc#) b
from bina8
group by doc#
/
DOC#  Notes
----- -------------------------------
1a    start of note
2a    I hope this does what you want.

2 rows selected.

Let me know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 07:09 (06May04) UTC (aka "GMT" and "Zulu"), 00:09 (06May04) Mountain Time)