Listagg in order and including a sequence of separator when missing any row of a sequence

csvlistaggoracleoracle-12c

Sorry for the long title but it's a little bit difficult to explain.

We have a table

| N rows | Atrib1  |    Atrib2 | Atrib3 | Atrib4 |  Final row |
---------------------------------------------------------------
| N1    |   R     |     X       | One   |  A1    |     True   |
| N1    |   D     |     Y       |       |        |     False  |
| N1    |   K     |     Z       |       |        |     False  |
| N1    |   N     |     A       |       |        |     True   |
---------------------------------------------------------------

We must aggregate all the Atribs column into a single line with ';' separator in the order marked in the table, because we are trying to do an export to a CSV file.

We have no problem with the aggregation but there is a special condition that we must follow. If a row or more rows, from sequence (R, D, K, N) for Atrib1, doesn't exist we must even include a set of ';' for each Atrib(1..4).

The query that we have already works fine if all the rows are filled but not when someone is missing.

select N rows, LISTAGG(Atrib1 ||';'|| coalesce(Atrib2,'') ||';'|| coalesce(Atrib3,'') ||';'|| coalesce(Atrib4,''), ';') WITHIN GROUP (ORDER BY Code_crit_0) "Employees", Final row 
from TABLE
group by N rows
order by decode(Code_crit_0, 'R',1,'D',2,'K',3,'N',4, 5) asc;

Any idea of how to accomplished this special condition without any SQL procedure.

Thanks in advance.

Best Answer

We have no problem with the aggregation but there is a special condition that we must follow. If a row or more rows, from sequence (R, D, K, N) for Atrib1, doesn't exist we must even include a set of ';' for each Atrib(1..4).

Suppose you have the following table (called TEST). Some of the the "nrows" - N2, N3, N4, N5 - do not have a complete set of (R,D,K,N) values.

create table test ( nrows, a1, a2, a3, a4, final )
as
select 'N1', 'R', 'X', 'One', 'A1', 'True' from dual union all
select 'N1', 'D', 'Y', null, null, 'False' from dual union all
select 'N1', 'K', 'Z', null, null, 'False' from dual union all
select 'N1', 'N', 'A', null, null, 'True'  from dual union all
-- N2: D, K, N missing
select 'N2', 'R', 'X', 'two', 'B1', 'True' from dual union all
-- N3: R, K, N missing
select 'N3', 'D', 'Y', null, null, 'False' from dual union all
-- N4: D, R, N missing
select 'N4', 'K', 'Z', null, null, 'False' from dual union all
-- N5: D, K, R missing
select 'N5', 'N', 'A', null, null, 'True'  from dual ;


select * from test ;
NROWS  A1  A2  A3    A4    FINAL  
N1     R   X   One   A1    True   
N1     D   Y   NULL  NULL  False  
N1     K   Z   NULL  NULL  False  
N1     N   A   NULL  NULL  True   
N2     R   X   two   B1    True   
N3     D   Y   NULL  NULL  False  
N4     K   Z   NULL  NULL  False  
N5     N   A   NULL  NULL  True

As a first step, you could write a query that generates the missing nrows/A1 combinations (NA1), and LEFT JOIN it to the original (O) table. 

select *
from (  
  select nrows, a1
  from ( 
     select unique a1 from test 
  ) cross join (
     select unique nrows from test
  )
) NA1 
left join  (
   select nrows, a1, a2, a3, a4, final from test
) O
on NA1.nrows = O.nrows and NA1.a1 = O.a1 
;
-- result
NROWS  A1  NROWS  A1    A2    A3    A4    FINAL  
N1     R   N1     R     X     One   A1    True   
N1     D   N1     D     Y     NULL  NULL  False  
N1     K   N1     K     Z     NULL  NULL  False  
N1     N   N1     N     A     NULL  NULL  True   
N2     R   N2     R     X     two   B1    True   
N3     D   N3     D     Y     NULL  NULL  False  
N4     K   N4     K     Z     NULL  NULL  False  
N5     N   N5     N     A     NULL  NULL  True   
N2     K   NULL   NULL  NULL  NULL  NULL  NULL   
N2     N   NULL   NULL  NULL  NULL  NULL  NULL   
N4     D   NULL   NULL  NULL  NULL  NULL  NULL   
N3     R   NULL   NULL  NULL  NULL  NULL  NULL   
N2     D   NULL   NULL  NULL  NULL  NULL  NULL   
N3     N   NULL   NULL  NULL  NULL  NULL  NULL   
N3     K   NULL   NULL  NULL  NULL  NULL  NULL   
N4     N   NULL   NULL  NULL  NULL  NULL  NULL   
N4     R   NULL   NULL  NULL  NULL  NULL  NULL   
N5     D   NULL   NULL  NULL  NULL  NULL  NULL   
N5     K   NULL   NULL  NULL  NULL  NULL  NULL   
N5     R   NULL   NULL  NULL  NULL  NULL  NULL   
-- 20 rows selected.

Then, use LISTAGG and DECODE (if CASE ... , as used in the example below, is too "bulky") and replace the NULLs with ;

select 
  NA1.nrows
, NA1.a1    as "RDKN"
, listagg ( O.a1 || ';' ||
    case when O.a2 is null then '' else O.a2 end || ';' ||
    case when O.a3 is null then '' else O.a3 end || ';' ||
    case when O.a4 is null then '' else O.a4 end || ';'
  , ';' ) within group ( order by O.a1 ) "Attribs 1-4"
from (  
  select nrows, a1
  from ( 
     select unique a1 from test 
  ) cross join (
     select unique nrows from test
  )
) NA1 
left join  (
   select nrows, a1, a2, a3, a4, final from test
) O
on NA1.nrows = O.nrows and NA1.a1 = O.a1 
group by NA1.nrows, NA1.a1
;

Result

NROWS  RDKN  Attribs 1-4  
N1     D     D;Y;;;       
N1     K     K;Z;;;       
N1     N     N;A;;;       
N1     R     R;X;One;A1;  
N2     D     ;;;;         
N2     K     ;;;;         
N2     N     ;;;;         
N2     R     R;X;two;B1;  
N3     D     D;Y;;;       
N3     K     ;;;;         
N3     N     ;;;;         
N3     R     ;;;;         
N4     D     ;;;;         
N4     K     K;Z;;;       
N4     N     ;;;;         
N4     R     ;;;;         
N5     D     ;;;;         
N5     K     ;;;;         
N5     N     N;A;;;       
N5     R     ;;;;         

20 rows selected.

Tested with Oracle 12c, and Oracle 11g. Dbfiddle here.

Related Question