Forum Stats

  • 3,728,151 Users
  • 2,245,559 Discussions
  • 7,853,355 Comments

Discussions

columns and group results

User_AC3QE
User_AC3QE Member Posts: 28 Red Ribbon
edited April 13 in SQL & PL/SQL
Hi

I need the nr_atend_origem field to show all the links between nr_atend_origem and the nr_atend_additional field

example:

In line 3 in nr_sequencia 9
the field nr_atend_origem = 10990250 links with the field nr_atend_additional = 10990249, then in nr_sequencia 10 and 11 the field nr_atend_origem = 10990251 links with the fields nr_atend_adional 10990250 and 10990249

I need to make a filter where it only displays a row or column like all links example

10990251, 10990250,1099249

the other should be

1099065, 10990063,10990235,10990249



I can have the same code in nr_atend_origem and after that code become the nr_atend_additional of another numeric code.

I need to list this relationship group between nr_atend_origem and nr_atend_additional

CREATE TABLE DIE_NR_ATEND 
   (NR_SEQUENCIA NUMBER(10,0) NOT NULL, 
	DT_ATUALIZACAO DATE NOT NULL, 
	NM_USUARIO VARCHAR2(15 BYTE) NOT NULL, 
	DT_ATUALIZACAO_NREC DATE, 
	NM_USUARIO_NREC VARCHAR2(15 BYTE), 
	NR_ATEND_ORIGEM NUMBER(10,0) NOT NULL , 
	NR_ATEND_ADICIONAL NUMBER(10,0) NOT NULL 
   );


Insert into DIE_NR_ATEND  (NR_SEQUENCIA,DT_ATUALIZACAO,NM_USUARIO,DT_ATUALIZACAO_NREC,NM_USUARIO_NREC,NR_ATEND_ORIGEM,NR_ATEND_ADICIONAL) values ('7',to_date('06/04/21 10:54:30','DD/MM/RR HH24:MI:SS'),'testeuser',to_date('06/04/21 10:54:22','DD/MM/RR HH24:MI:SS'),'testeuser','10990065','10990063');
Insert into DIE_NR_ATEND  (NR_SEQUENCIA,DT_ATUALIZACAO,NM_USUARIO,DT_ATUALIZACAO_NREC,NM_USUARIO_NREC,NR_ATEND_ORIGEM,NR_ATEND_ADICIONAL) values ('8',to_date('09/04/21 11:48:48','DD/MM/RR HH24:MI:SS'),'testeuser',to_date('09/04/21 11:48:39','DD/MM/RR HH24:MI:SS'),'testeuser','10990065','10990235');
Insert into DIE_NR_ATEND  (NR_SEQUENCIA,DT_ATUALIZACAO,NM_USUARIO,DT_ATUALIZACAO_NREC,NM_USUARIO_NREC,NR_ATEND_ORIGEM,NR_ATEND_ADICIONAL) values ('9',to_date('12/04/21 08:03:12','DD/MM/RR HH24:MI:SS'),'testeuser',to_date('12/04/21 08:03:08','DD/MM/RR HH24:MI:SS'),'testeuser','10990250','10990249');
Insert into DIE_NR_ATEND  (NR_SEQUENCIA,DT_ATUALIZACAO,NM_USUARIO,DT_ATUALIZACAO_NREC,NM_USUARIO_NREC,NR_ATEND_ORIGEM,NR_ATEND_ADICIONAL) values ('10',to_date('12/04/21 08:05:50','DD/MM/RR HH24:MI:SS'),'testeuser',to_date('12/04/21 08:05:48','DD/MM/RR HH24:MI:SS'),'testeuser','10990251','10990249');
Insert into DIE_NR_ATEND  (NR_SEQUENCIA,DT_ATUALIZACAO,NM_USUARIO,DT_ATUALIZACAO_NREC,NM_USUARIO_NREC,NR_ATEND_ORIGEM,NR_ATEND_ADICIONAL) values ('11',to_date('12/04/21 08:05:54','DD/MM/RR HH24:MI:SS'),'testeuser',to_date('12/04/21 08:05:50','DD/MM/RR HH24:MI:SS'),'testeuser','10990251','10990250');
Insert into DIE_NR_ATEND  (NR_SEQUENCIA,DT_ATUALIZACAO,NM_USUARIO,DT_ATUALIZACAO_NREC,NM_USUARIO_NREC,NR_ATEND_ORIGEM,NR_ATEND_ADICIONAL) values ('12',to_date('12/04/21 11:09:17','DD/MM/RR HH24:MI:SS'),'testeuser',to_date('12/04/21 11:09:16','DD/MM/RR HH24:MI:SS'),'testeuser','10990256','10990254');
Insert into DIE_NR_ATEND  (NR_SEQUENCIA,DT_ATUALIZACAO,NM_USUARIO,DT_ATUALIZACAO_NREC,NM_USUARIO_NREC,NR_ATEND_ORIGEM,NR_ATEND_ADICIONAL) values ('15',to_date('12/04/21 16:28:12','DD/MM/RR HH24:MI:SS'),'testeuser',to_date('12/04/21 16:28:12','DD/MM/RR HH24:MI:SS'),'testeuser','10990065','10990249');


Best Answer

  • Paulzip
    Paulzip Member Posts: 8,245 Blue Diamond
    edited April 13 Accepted Answer

    If you don't care about list order, and I've understood you...

    select a.*, listagg(nr_atend_adicional, ',') within group (order by nr_sequencia) over (partition by nr_atend_origem) || nullif(','||case when nr_atend_origem <> nr_atend_adicional then nr_atend_origem end, ',') list
    from die_nr_atend a;
    

    If list order is important and assuming you aren't on 19c

    with unpivdata as (
      select *
      from (
        select a.*, nr_atend_origem as nr_atend_origem2
        from   die_nr_atend a
      )
      unpivot (
        nr_atend_origem2_eff
        for col_name in (
          nr_atend_adicional, nr_atend_origem2
        )
      ) u
    )
    , nulldupe as (
      select nr_sequencia, dt_atualizacao, nm_usuario, dt_atualizacao_nrec, nm_usuario_nrec, nr_atend_origem, col_name
           , case when row_number() over (partition by nr_atend_origem, nr_atend_origem2_eff order by nr_sequencia) = 1 then nr_atend_origem2_eff else null end nr_atend_origem2_eff  -- Only first unique value preserved
      from   unpivdata d
    )
    select distinct nr_sequencia, dt_atualizacao, nm_usuario, dt_atualizacao_nrec, nm_usuario_nrec, nr_atend_origem, list
    from ( 
      select nr_sequencia, dt_atualizacao, nm_usuario, dt_atualizacao_nrec, nm_usuario_nrec, nr_atend_origem
          , listagg(nr_atend_origem2_eff, ',') within group (order by nr_sequencia, nr_atend_origem2_eff) over (partition by nr_atend_origem) list
      from nulldupe 
    )
    order by 1;
    
    User_AC3QE

Answers

  • Paulzip
    Paulzip Member Posts: 8,245 Blue Diamond
    edited April 13 Accepted Answer

    If you don't care about list order, and I've understood you...

    select a.*, listagg(nr_atend_adicional, ',') within group (order by nr_sequencia) over (partition by nr_atend_origem) || nullif(','||case when nr_atend_origem <> nr_atend_adicional then nr_atend_origem end, ',') list
    from die_nr_atend a;
    

    If list order is important and assuming you aren't on 19c

    with unpivdata as (
      select *
      from (
        select a.*, nr_atend_origem as nr_atend_origem2
        from   die_nr_atend a
      )
      unpivot (
        nr_atend_origem2_eff
        for col_name in (
          nr_atend_adicional, nr_atend_origem2
        )
      ) u
    )
    , nulldupe as (
      select nr_sequencia, dt_atualizacao, nm_usuario, dt_atualizacao_nrec, nm_usuario_nrec, nr_atend_origem, col_name
           , case when row_number() over (partition by nr_atend_origem, nr_atend_origem2_eff order by nr_sequencia) = 1 then nr_atend_origem2_eff else null end nr_atend_origem2_eff  -- Only first unique value preserved
      from   unpivdata d
    )
    select distinct nr_sequencia, dt_atualizacao, nm_usuario, dt_atualizacao_nrec, nm_usuario_nrec, nr_atend_origem, list
    from ( 
      select nr_sequencia, dt_atualizacao, nm_usuario, dt_atualizacao_nrec, nm_usuario_nrec, nr_atend_origem
          , listagg(nr_atend_origem2_eff, ',') within group (order by nr_sequencia, nr_atend_origem2_eff) over (partition by nr_atend_origem) list
      from nulldupe 
    )
    order by 1;
    
    User_AC3QE
  • User_AC3QE
    User_AC3QE Member Posts: 28 Red Ribbon
    edited April 13


    thank you for the explanation
    
    only one point should exhibit an adjustment
    
    because the source field (nr_atend_origem) 10990250 is linked with the additional field
    (nr_atend_additional)
    1099249
    
    after is the additional field (nr_atend_additional) of the source field (nr_atend_origem) 10990251
    



    this line should display 10990249,10990250,10990251
    
  • Paulzip
    Paulzip Member Posts: 8,245 Blue Diamond

    Did you try my second query?

  • User_AC3QE
    User_AC3QE Member Posts: 28 Red Ribbon

    yes, I consulted

    it still has the same value.or

    should bringshould display 10990249,10990250,10990251


    or should I bring it just like that


    only a single line from each group


  • Paulzip
    Paulzip Member Posts: 8,245 Blue Diamond

    I don't follow your logic it's not at all clear, (it may be to you, as you know your problem domain), but it certainly isn't to me.

  • User_AC3QE
    User_AC3QE Member Posts: 28 Red Ribbon


    sorry, I will try to explain the logic, I need the numbers from the beginning not to be repeated, and take the field sequence (nr_sequencia) with a larger number.

    showing only these


  • Paulzip
    Paulzip Member Posts: 8,245 Blue Diamond
    edited April 13

    Your explanation still isn't at all clear to me and probably explains why others haven't attempted an answer.

    "I need the numbers from the beginning"

    Define beginning.

    "..not to be repeated"

    No idea what you mean by this.

    " and take the field sequence (nr_sequencia) with a larger number."

    ???

    What are your rules? Using your data, show us how you work through your example to get the results you expect and how they come about. If I don't see a clear explanation in your next reply, I'm afraid I'm not spending any more time on this.

    User_AC3QE
  • User_AC3QE
    User_AC3QE Member Posts: 28 Red Ribbon
    edited April 13


    thank you, I believe that with this query, it will be clear
    
    be able to do using a pck.
    
    that's what I need
    


Sign In or Register to comment.