How to concat cells from same table but different row?

Question

I have the following problem. I have some rows in my table which are nearly the same and i need some cells to concat with cells above. My current Select statement looks like this:

Select bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltgs6, bltugp
      ,regexp_replace(regexp_replace(LISTAGG(bltxt,' '),'\s+',' '),'¯+','') AS Text 
from atdata.bip105
where bltspriso = 'DEAT'
group by bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltgs6, blttkz, bltugp
order by bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltgs6, blttkz, bltugp

The output look like this

BLTGS1-6 work like categories, for example the first entry looks like (0 = empty/null): "1-0-0-0-0-0-0-0 AKTIVA" the second entry looks like "1-1-0-0-0-0-0 BTIVA". SO BTIVA is a sub-category from AKTIVA. There are some rows which contain 1 or 2 in BLTUGP. If this is the case, i would like to concatinate the TEXT entry to the first row above which doesn't contain a number in BLTUGP. So concerning the text, it should look like, for example:

BLTUGP| TEXT
      | TOM
 1    | likes salat.
 2    | likes tomatoes.

The output of this example should look like:

BLTUGP| TEXT
      | TOM
 1    | TOM likes salat.
 2    | TOM like tomatoes.

As you see for example the row with "erhaltene Anzahlungen auf Bestellung" should look like:

2 - 1 - 12 - 01 - 007 - 01 - "erhaltene Anzahlungen auf Bestellung davon mit einer Restlaufzeit von bis zu einem Jahr"

and

2 - 1 - 12 - 01 - 007 - 02 - "erhaltene Anzahlungen auf Bestellung davon mit einer Restlaufzeit von mehr als einem Jahr"

....but I also need all the rows where bltugp is empty/null. Additional information! I can't manipulate/change the source table.

Thanks in advance!

Update: I tried

with 

tbl_wougp as

  (Select bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltgs6, bltugp ,regexp_replace(regexp_replace(LISTAGG(bltxt,' '),'\s+',' '),'¯+','') AS Text

    from atdata.bip105

    where bltspriso = 'DEAT' and bltugp=''

    group by bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltgs6, blttkz, bltugp),

tbl_wugp as

  (Select u.bltgs1, u.bltgs2, u.bltgs3, u.bltgs4, u.bltgs5, u.bltgs6, u.bltugp, concat(concat(trim(h.bltxt), ' '), trim(u.bltxt))  as Text from 

    (select * from atdata.bip105 where bltspriso = 'DEAT' and bltugp='') h right join

    (select * from atdata.bip105 where bltspriso = 'DEAT' and bltugp<>'') u

   on u.bltgs1=h.bltgs1 and u.bltgs2=h.bltgs2 and u.bltgs3=h.bltgs3 and u.bltgs4=h.bltgs4 and u.bltgs5=h.bltgs5 and u.bltgs6=u.bltgs6 

)

select * from tbl_wougp

union 

select * from tbl_wugp

order by bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltgs6;

which nearly works as expected but some rows seem to double and i don't no why.

Please don't post images. Not everybody has access to image dump sites. Furthermore it would be helpful if you were to post sample input data. It's hard to visualise how to get a desired result without understanding your starting point. — APC, Feb 19 '19 at 15:28
You get multiple rows because you're including all the other columns in the group-by; you may need to aggregate `bltgup` instead, for instance. But taking the two rows you pulled out as text, for 2/1/12/01/007 you have one row with `bltgup` null, so what is your logic - prepend that null-value text to all other non-null-value row's text? — Alex Poole, Feb 19 '19 at 15:37
@AlexPoole this is for a balance sheet report and there are some accounts which mean nearly the same. The difference between them is that one has a remaining time under 1 year and the other one more than 1 year but I also need the rows where bltgup is null — F.Gradl, Feb 19 '19 at 16:12
Please edit your question to fully explain the logic. For `bltgs5 = '007'` you have three rows, one null and two not-null; your example output suggests you only want to see the not-null rows, but with the null value prepended. Looking at the image (!) again you have a different scenario for `bltgs5 = '021'`, several rows where `bltgs6` is set which seem to follow that pattern, but also a single row where both `bltgs6` and `bltugp` are null. So do you want to see that single row in the output too? Or does something else happen with that? — Alex Poole, Feb 20 '19 at 11:21
@AlexPoole Sorry for the picture. I'll try to explain it a little bit more. The numbers (bltgs) work like categories. For example (0 means empty): "1-0-0-0-0-0 Aktiva" is the highest category and "1-1-01-0-0-0 Anlagenvermögen" is a sub-category from Aktiva and so on.. so as you can see in the picture "2-1-12-01-21-0-0 sonstige Verbindlichkeiten" contains all rows as sub-categories till bltgs5 (number 21) chances to, for example, 22. — F.Gradl, Feb 20 '19 at 12:10
Please edit your question, don't try to put everything in comments. It would also still be more helpful to include sample data and the expected results for that data in the question, as formatted text. (A db<>fiddle or similar would also be helpful, not but vital.) — Alex Poole, Feb 20 '19 at 12:12
@AlexPoole and yes, i want to see all rows. but i want that the text from a row where "bltugp" is 1 or 2, to be concatinated to the first row above them which has "null/empty" in bltugp. I hope you can follow me, sorry for my bad explanaition skills. — F.Gradl, Feb 20 '19 at 12:17

Gopi · Answer 1 · 2019-02-19T15:17:46.630

0

You can use LISTAGG (starting Oracle 11.2) to do this. Here is the link on how to do this.

If you're running on lower version of Oracle you could use WM_CONCAT if it is supported.

edited Feb 19 '19 at 15:17

answered Feb 19 '19 at 15:12

Gopi

620
8
16

2

The code posted by the OP already uses `listagg()`. You need to explain they can use it better. – APC Feb 19 '19 at 15:26

Alex Poole · Answer 2 · 2019-02-20T12:28:41.453

You appear to want, for each combination of other column values, to have the text associated with a null bltugp to be prepended to the text associated with all non-null values.

One way to do that is with concatenation and an analytic function to find the null-based text to prepend:

max(case when bltugp is null then bltxt end)
        over (partition by bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltgs6, blttkz)
      || bltxt

which would give you three rows:

    BLTGS1     BLTGS2     BLTGS3 BL BLT BLTGS6 BL TEXT                                                                                                    
---------- ---------- ---------- -- --- ------ -- --------------------------------------------------------------------------------------------------------
         2          1         12 01 007        01 erhaltene Anzahlungen auf Bestellungdavon mit einer Restlaufzeit von bis zu einem Jahr                  
         2          1         12 01 007        02 erhaltene Anzahlungen auf Bestellungdavon mit einer Restlaufzeit von mehr als einem Jahr                
         2          1         12 01 007           erhaltene Anzahlungen auf Bestellungerhaltene Anzahlungen auf Bestellung

... and then discard the one you don't want. As an example with those and a couple of other maybe-interesting rows as sample data:

-- CTE for dummy data
with bip105 (bltspriso, blttkz, bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltgs6, bltugp, bltxt) as (
  select 'DEAT', 42, 2, 1, 12, '01', '006', null, '02', 'davon mit einer Restlaufzeit von mehr als einem Jahr' from dual
  union all
  select 'DEAT', 42, 2, 1, 12, '01', '007', null, null, 'erhaltene Anzahlungen auf Bestellung' from dual
  union all
  select 'DEAT', 42, 2, 1, 12, '01', '007', null, '01', 'davon mit einer Restlaufzeit von bis zu einem Jahr' from dual
  union all
  select 'DEAT', 42, 2, 1, 12, '01', '007', null, '02', 'davon mit einer Restlaufzeit von mehr als einem Jahr' from dual
  union all
  select 'DEAT', 42, 2, 1, 12, '01', '021', null, null, 'sonstige Verbindlichkeiten' from dual
)
-- actual query
select bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltgs6, bltugp, text
from (
  select bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltgs6, blttkz, bltugp
    , max(case when bltugp is null then bltxt end)
        over (partition by bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltgs6, blttkz)
      || bltxt as text
  from bip105
  where bltspriso = 'DEAT'
)
where bltugp is not null
order by bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltgs6, blttkz, bltugp;

    BLTGS1     BLTGS2     BLTGS3 BL BLT BLTGS6 BL TEXT                                                                                                    
---------- ---------- ---------- -- --- ------ -- --------------------------------------------------------------------------------------------------------
         2          1         12 01 006        02 davon mit einer Restlaufzeit von mehr als einem Jahr                                                    
         2          1         12 01 007        01 erhaltene Anzahlungen auf Bestellungdavon mit einer Restlaufzeit von bis zu einem Jahr                  
         2          1         12 01 007        02 erhaltene Anzahlungen auf Bestellungdavon mit einer Restlaufzeit von mehr als einem Jahr

If you want to show all of the null-based rows as well you can remove the filter, but also need to stop that value being prepended to itself; probably simpler ways but this is one using another case expression:

select bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltgs6, bltugp, text
from (
  select bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltgs6, blttkz, bltugp
    , max(case when bltugp is null then bltxt end)
        over (partition by bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltgs6, blttkz)
      || case when bltugp is not null then bltxt end as text
  from bip105
  where bltspriso = 'DEAT'
)
order by bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltgs6, blttkz, bltugp

    BLTGS1     BLTGS2     BLTGS3 BL BLT BLTGS6 BL TEXT                                                                                                    
---------- ---------- ---------- -- --- ------ -- --------------------------------------------------------------------------------------------------------
         2          1         12 01 006        02 davon mit einer Restlaufzeit von mehr als einem Jahr                                                    
         2          1         12 01 007        01 erhaltene Anzahlungen auf Bestellungdavon mit einer Restlaufzeit von bis zu einem Jahr                  
         2          1         12 01 007        02 erhaltene Anzahlungen auf Bestellungdavon mit einer Restlaufzeit von mehr als einem Jahr                
         2          1         12 01 007           erhaltene Anzahlungen auf Bestellung                                                                    
         2          1         12 01 021           sonstige Verbindlichkeiten

Or if you only want to see the null-based rows when there are no others for that combination, you could count the non-null entries for each combination, and only exclude the nulls if any non-null exist (implying the null one has been prepended and isn't needed on its own - if that is indeed the rule you need):

select bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltgs6, bltugp, text
from (
  select bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltgs6, blttkz, bltugp
    , max(case when bltugp is null then bltxt end)
        over (partition by bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltgs6, blttkz)
      || case when bltugp is not null then bltxt end as text
    , count(bltugp) over (partition by bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltgs6, blttkz)
      as non_null_count
  from bip105
  where bltspriso = 'DEAT'
)
where non_null_count = 0 or bltugp is not null
order by bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltgs6, blttkz, bltugp;

    BLTGS1     BLTGS2     BLTGS3 BL BLT BLTGS6 BL TEXT                                                                                                    
---------- ---------- ---------- -- --- ------ -- --------------------------------------------------------------------------------------------------------
         2          1         12 01 006        02 davon mit einer Restlaufzeit von mehr als einem Jahr                                                    
         2          1         12 01 007        01 erhaltene Anzahlungen auf Bestellungdavon mit einer Restlaufzeit von bis zu einem Jahr                  
         2          1         12 01 007        02 erhaltene Anzahlungen auf Bestellungdavon mit einer Restlaufzeit von mehr als einem Jahr                
         2          1         12 01 021           sonstige Verbindlichkeiten

It still isn't really clear what you want to see...

score 0 · Answer 3 · answered Feb 19 '19 at 16:03

Self-join your current result, like in this example:

-- sample data
with your_query(bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltugp, text) as (
  select 2, 1, 12, '01', '007', null, 'Tom wants to'  from dual union all
  select 2, 1, 12, '01', '007', '01', 'sleep'         from dual union all
  select 2, 1, 12, '01', '007', '02', 'play'          from dual union all
  select 2, 1, 12, '01', '008', null, 'Mark is'       from dual union all
  select 2, 1, 12, '01', '008', '01', 'cheerful'      from dual union all
  select 2, 1, 12, '01', '008', '02', 'sad'           from dual )
-- end of sample data

select bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, b.bltugp,
       a.text||' '||b.text text
  from your_query a 
  join your_query b using (bltgs1, bltgs2, bltgs3, bltgs4, bltgs5)
  where (a.bltugp is null and b.bltugp = '01') 
     or (a.bltugp is null and b.bltugp = '02')
  order by bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, b.bltugp

...and we get:

BLTGS1     BLTGS2     BLTGS3 BLTGS4 BLTGS5 BLTUGP TEXT
------ ---------- ---------- ------ ------ ------ -------------------------
     2          1         12 01     007    01     Tom wants to sleep
     2          1         12 01     007    02     Tom wants to play
     2          1         12 01     008    01     Mark is cheerful
     2          1         12 01     008    02     Mark is sad

score 0 · Accepted Answer · answered Feb 21 '19 at 10:57

I managed to find a solution

with 

tbl_wougp as
(Select bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltgs6, bltugp ,regexp_replace(regexp_replace(LISTAGG(bltxt,' '),'\s+',' '),'¯+','') AS Text
from atdata.bip105
where bltspriso = 'DEAT' and bltugp=''
group by bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltgs6, blttkz, bltugp),

tbl_wugp as
(Select bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltgs6, bltugp ,regexp_replace(regexp_replace(LISTAGG(bltxt,' '),'\s+',' '),'¯+','') AS Text
from atdata.bip105
where bltspriso = 'DEAT' and bltugp<>''
group by bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltgs6, blttkz, bltugp)

select * from tbl_wougp
union
select u.bltgs1, u.bltgs2, u.bltgs3, u.bltgs4, u.bltgs5, u.bltgs6, u.bltugp, concat(concat(trim(h.text), ' '), trim(u.text)) as Text
from tbl_wugp u left join tbl_wougp h
on u.bltgs1=h.bltgs1 and u.bltgs2=h.bltgs2 and u.bltgs3=h.bltgs3 and u.bltgs4=h.bltgs4 and u.bltgs5=h.bltgs5 and u.bltgs6=h.bltgs6 
order by bltgs1, bltgs2, bltgs3, bltgs4, bltgs5, bltgs6, bltugp, text;

How to concat cells from same table but different row?

4 Answers4