-- ©2006 Edward Stoever 



-- This requires installing the package apkgenr.flatten

CREATE TABLE FTS_AWARDS (GRANTOR_NAME  VARCHAR2(60), 
                         RECEIVER_NAME VARCHAR2(93), 
						 AWARD_AMOUNT  NUMBER(11,2)
						 ) TABLESPACE users;
						 
create public synonym fts_awards for fts_awards;
Synonym Created
						 
INSERT INTO FTS_AWARDS ( GRANTOR_NAME, RECEIVER_NAME, AWARD_AMOUNT ) VALUES ( 
'C Family Scholarship', 'Butts, Paige', 1200); 
INSERT INTO FTS_AWARDS ( GRANTOR_NAME, RECEIVER_NAME, AWARD_AMOUNT ) VALUES ( 
'C Family Scholarship', 'Stato, Kelly', 1200); 
INSERT INTO FTS_AWARDS ( GRANTOR_NAME, RECEIVER_NAME, AWARD_AMOUNT ) VALUES ( 
'C Family Scholarship', 'Zha, Shen', 1200); 
INSERT INTO FTS_AWARDS ( GRANTOR_NAME, RECEIVER_NAME, AWARD_AMOUNT ) VALUES ( 
'C Memorial Scholarship', 'Cornwell, David', 1300); 
INSERT INTO FTS_AWARDS ( GRANTOR_NAME, RECEIVER_NAME, AWARD_AMOUNT ) VALUES ( 
'C Memorial Scholarship', 'Meve, Elizabeth', 1300); 
INSERT INTO FTS_AWARDS ( GRANTOR_NAME, RECEIVER_NAME, AWARD_AMOUNT ) VALUES ( 
'C Memorial Scholarship', 'Schu, Paul', 1300); 
INSERT INTO FTS_AWARDS ( GRANTOR_NAME, RECEIVER_NAME, AWARD_AMOUNT ) VALUES ( 
'C Thompson Noodle Scholarship Fund', 'Cornwell, Joseph Q.', 1100); 
INSERT INTO FTS_AWARDS ( GRANTOR_NAME, RECEIVER_NAME, AWARD_AMOUNT ) VALUES ( 
'M Jubilee Scholarship Fund', 'Christal, Ruth', 3000); 
COMMIT;

SET linesize 100
SET pagesize 100
col GRANTOR_NAME FOR a30 trun
col RECEIVER_NAME FOR a25 trun
col AWARD_AMOUNT FOR 999999999.99
SELECT * FROM FTS_AWARDS;

GRANTOR_NAME                   RECEIVER_NAME              AWARD_AMOUNT
------------------------------ ------------------------- -------------
C Family Scholarship           Butts, Paige                    1200.00
C Family Scholarship           Stato, Kelly                    1200.00
C Family Scholarship           Zha, Shen                       1200.00
C Memorial Scholarship         Cornwell, David                 1300.00
C Memorial Scholarship         Meve, Elizabeth                 1300.00
C Memorial Scholarship         Schu, Paul                      1300.00
C Thompson Noodle Scholarship  Cornwell, Joseph Q.             1100.00
M Jubilee Scholarship Fund     Christal, Ruth                  3000.00

8 rows selected.


SET linesize 90
col "AWARD DETAILS" FOR a90
SELECT DISTINCT apkgenr.flatten
      ('SELECT RECEIVER_NAME, AWARD_AMOUNT FROM fts_awards WHERE GRANTOR_NAME=''C Family Scholarship''',
       ': '
       ) AS "AWARD DETAILS"
           FROM FTS_AWARDS;

AWARD DETAILS                                                                   
--------------------------------------------------------------------------------
Butts, Paige: 1200: Stato, Kelly: 1200: Zha, Shen: 1200                         

1 row selected.		   


SET linesize 90
col "AWARD DETAILS" FOR a90
SELECT DISTINCT    grantor_name
               || ': '
               || apkgenr.flatten
                     (   'SELECT  RECEIVER_NAME ||'' $''|| AWARD_AMOUNT FROM fts_awards WHERE GRANTOR_NAME='''
                      || grantor_name
                      || '''',
                      ', '
                     ) AS "AWARD DETAILS"
          FROM FTS_AWARDS; 

AWARD DETAILS                                                                             
------------------------------------------------------------------------------------------
C Family Scholarship: Butts, Paige $1200, Stato, Kelly $1200, Zha, Shen $1200             
C Memorial Scholarship: Cornwell, David $1300, Meve, Elizabeth $1300, Schu, Paul $1300    
C Thompson Noodle Scholarship Fund: Cornwell, Joseph Q. $1100                             
M Jubilee Scholarship Fund: Christal, Ruth $3000                                          

4 rows selected.

-- TAB DELIMITED:
col "AWARD DETAILS" FOR a100 
SET linesize 100
SELECT DISTINCT grantor_name
             || CHR(9)  --tab
             || apkgenr.flatten
             ('SELECT RECEIVER_NAME, ''$'' || AWARD_AMOUNT  FROM fts_awards WHERE GRANTOR_NAME='''
                    || grantor_name
                    || '''',
                    CHR(9) --tab
                   ) AS "AWARD DETAILS"
        FROM FTS_AWARDS; 

AWARD DETAILS                                                                                       
----------------------------------------------------------------------------------------------------
C Family Scholarship	Butts, Paige	$1200	Stato, Kelly	$1200	Zha, Shen	$1200                          
C Memorial Scholarship	Cornwell, David	$1300	Meve, Elizabeth	$1300	Schu, Paul	$1300                 
C Thompson Noodle Scholarship Fund	Cornwell, Joseph Q.	$1100                                        
M Jubilee Scholarship Fund	Christal, Ruth	$3000                                                     

4 rows selected.


col "AWARD DETAILS" FOR a105 
SET linesize 105
SELECT DISTINCT    ''''||grantor_name||''''
               || ', '
               || apkgenr.flatten
                     (   'SELECT  '''''''' || RECEIVER_NAME || '''''''' , ''''''$'' || AWARD_AMOUNT || ''''''''  FROM fts_awards WHERE GRANTOR_NAME='''
                      || grantor_name
                      || '''',
                      ', '
                     ) AS "AWARD DETAILS"
          FROM FTS_AWARDS;


AWARD DETAILS                                                                                            
---------------------------------------------------------------------------------------------------------
'C Family Scholarship', 'Butts, Paige', '$1200', 'Stato, Kelly', '$1200', 'Zha, Shen', '$1200'           
'C Memorial Scholarship', 'Cornwell, David', '$1300', 'Meve, Elizabeth', '$1300', 'Schu, Paul', '$1300'  
'C Thompson Noodle Scholarship Fund', 'Cornwell, Joseph Q.', '$1100'                                     
'M Jubilee Scholarship Fund', 'Christal, Ruth', '$3000'                                                  

4 rows selected.