Identify NULL and assign bitmap

Question

I have a table

table1

u_a_id  e_id   e_nm    e_val    e_seq
1       104    test    100      4
1       102    test2   x        2
1       102    test2   (null)   1
1       104    test    (null)   1

2       102    test2   (null)   2
2       102    test2   (null)   1
2       104    test    101      1
2       104    test    102      2

I need to first sort by e_id, e_seq for each u_a_id and identify each (null) as 1 and then create a bitmap as below.

For ex.

• u_a_id = 1 - first e_id = 102, e_seq = 1, e_val = (null), so assign it 1
• u_a_id = 1 - first e_id = 102, e_seq = 2, e_val = x, so assign it 0
• u_a_id = 1 - first e_id = 104, e_seq = 1, e_val = (null), so assign it 1
• u_a_id = 1 - first e_id = 104, e_seq = 4, e_val = 100, so assign it 0

So, for u_a_id = 1, add a new row - EMPTY = 1010

The output would be:

u_a_id  e_id   e_nm    e_val    e_seq
1       104    test    100      4
1       102    test2   x        2
1       102    test2   (null)   1
1       104    test    (null)   1
1       (null) EMPTY   1010     (null)

2       102    test2   (null)   2
2       102    test2   (null)   1
2       104    test    101      1
2       104    test    102      2
2       (null) EMPTY   1100     (null)

Is there a way in Oracle SQL to do it?

Answer 1

You could use LISTAGG for string aggregation and GROUPING SETS:

SELECT U_A_ID, E_ID
 ,CASE WHEN GROUPING_ID(U_A_ID, E_ID, E_NM, E_VAL, E_SEQ) = 15 
       THEN 'EMPTY' 
       ELSE E_NM END AS E_NM
 ,CASE WHEN GROUPING_ID(U_A_ID, E_ID, E_NM, E_VAL, E_SEQ) = 15 
       THEN LISTAGG(NVL2(E_VAL, '1', '0'),'') WITHIN GROUP (ORDER BY E_ID DESC, E_SEQ DESC) 
       ELSE E_VAL END AS E_VAL
 ,E_SEQ
FROM E
GROUP BY GROUPING SETS ((U_A_ID, E_ID, E_NM, E_VAL, E_SEQ), (U_A_ID))
ORDER BY U_A_ID, E_ID, E_SEQ;

db<>fiddle demo