How to aggregate rows and converts into columns?

Question

There is a a parsed table table_1:

|id | parent_id | name    | value |
+---+-----------+---------+-------+  
| 1 | 0         | NumDoc  | 63    | 
| 2 | 0         | Groups  | null  |
| 4 | 3         | Group   | ALM   |
| 5 | 3         | Vals    | null  |
| 7 | 6         | ValCode | USD   |
|10 | 9         | ValCode | CHF   |
|11 | 3         | Group   | TLD   |
|12 | 3         | Vals    | null  |
|14 | 13        | ValCode | USD   |
|17 | 16        | ValCode | RUB   |

I am expecting a result like this:

| Numdoc | Group | Valcode |
+--------+------+----------+  
| 63     | ALM  | USD      |
| 63     | ALM  | CHF      |
| 63     | TLD  | USD      |
| 63     | TLD  | RUB      |

My sql select query:

with t as( select * from(  
       select (case when c.name='NumDoc' then c.Value end) NumDoc,
              (case when c.name='Group' then c.Value end) as Group, 
              (case when c.name='ValCode' then c.Value end) as ValCode,
               rownum as rn
         from  table_1 c))
select NumDoc, 
       min(Group), 
       min(ValCode)
from(select t.*, row_number() over (partition by NumDoc, rn order by rn) as seqnum 
     from t) T
group by NumDoc,seqnum,Group, ValCode

Result select:

| Numdoc | Group | Valcode |
+--------+-------+---------+  
| null   | null  | null    |
| null   | null  | CHF     |
| null   | null  | RUB     |
| null   | null  | USD     |
| null   | ALM   | null    |
| null   | TLD   | null    |
| 63     | null  | null    |

Then there's no way to know how rows are linked. Why TLD is linked to RUB but not to CHF for example? — Serg, Apr 23 '21 at 06:05
Without id table structure is incorrect , it's impossible to define to witch group valCode related @RealMan — Sergey Afinogenov, Apr 23 '21 at 06:12
Please post your original JSON as text not an image, and show how you are getting from the JSON to `table_1`. Do you actually need/want `table_1` - as getting from the JSON straight to your expected output is simple - depending on which version of Oracle you are using. — Alex Poole, Apr 23 '21 at 07:41
@RealMan - the `parent_id` values don't appear in the table as `id` values, so you still can't link them together. — Alex Poole, Apr 23 '21 at 08:56

Sergey Afinogenov · Accepted Answer · 2021-04-23T12:16:22.810

It's not clear to me how parent_id related to id.

But if we suggest that json is already parsed into table table_1 and ValCode's ids are always between it's group and next group id's, then :

select q1.value "Numdoc", 
       q2.value "Group", 
       q3.value "Valcode"  
 from (select id, value, lead(id) over (order by id) next_numdoc_id
         from table_1 
        where name='NumDoc') q1
 join (select id, value, lead(id) over (order by id) next_group_id
         from table_1 
        where name='Group') q2
   on q2.id between q1.id and nvl(q1.next_numdoc_id-1,q2.id)      
 join (select id, value
         from table_1 
        where name='ValCode') q3
   on q3.id between q2.id and nvl(q2.next_group_id-1,q3.id)     
 order by q1.value, q2.value, q3.value

https://dbfiddle.uk aggregating rows example

score 0 · Answer 2 · answered Apr 23 '21 at 08:02

0

If you're on Oracle 12c or above, it's simple to get from your original JSON (in its current state, or cleaned up) to your expected result with a single json_table call, using nested paths to get the child elements:

select jt.*
from json_table ('<your JSON>', '$'
   columns
      numdoc number path '$.NumDoc',
      nested path '$.Groups[*]'
         columns (
           groupcode varchar2(3) path '$.GroupCode',
           nested path '$.Vals[*]'
              columns (
                 valcode varchar2(3) path '$.StValCode'
              )
         )
) jt

NUMDOC | GROUPCODE | VALCODE
-----: | :-------- | :------
    63 | ALM       | USD    
    63 | ALM       | CHF    
    63 | TLD       | USD    
    63 | TLD       | RUB

db<>fiddle demo supplying the JSON from a CTE.

answered Apr 23 '21 at 08:02

Alex Poole

183,384
11
179
318

if i have oracle 11g – RealMan Apr 23 '21 at 09:04
OK, then this isn't available. Please update your question to show how you are handling the JSON now - which I imagine you're doing with some kind of horrible manual parse of the data rather than with e.g. the PL/json third-party package. – Alex Poole Apr 23 '21 at 09:12
how to pull out all the data without listing in columns ? – RealMan May 26 '21 at 07:35
Please ask a new question explaining what you are trying to do, with sample data, logic and expected results, and mention your DB version is that is quite old and restrictive now. – Alex Poole May 26 '21 at 07:37
https://stackoverflow.com/questions/67700708/parse-json-through-json-table-in-oracle-18 – RealMan May 26 '21 at 07:52

How to aggregate rows and converts into columns?

2 Answers2