SQL & PL/SQL

Please surround your data in your post by the tags [pre] and [/pre] to preserve the format, so that we know what data belongs to which column.

You may also do a search on this forum on "pivot" and "string aggregation". You'll find lots of helpful examples.

Regards,
Rob.

try

SELECT MAX(F1),MAX(F2),MAX(F3),MAX(F4)
FROM <>
GROUP BY F1

Check it --

satyaki>
satyaki>with t
  2  as
  3    (
  4      select 1 xx, 'A' yy, null vv, null zz from dual
  5      union all
  6      select 1, null, 'B', null from dual
  7      union all
  8      select 1, null, null, 'C' from dual
  9      union all
 10      select 2, 'D', null, null from dual
 11      union all
 12      select 2, null, 'E', null from dual
 13      union all
 14      select 2, null, null, 'F' from dual
 15      union all
 16      select 3, 'G', null, null from dual
 17      union all
 18      select 3, null, 'H', null from dual
 19      union all
 20      select 3, null, null, 'I' from dual
 21     )
 22  select t.xx, t.yy, t.vv, t.zz
 23  from t;

        XX Y V Z
---------- - - -
         1 A
         1   B
         1     C
         2 D
         2   E
         2     F
         3 G
         3   H
         3     I

9 rows selected.

satyaki>
satyaki>
satyaki>with t
  2  as
  3    (
  4      select 1 xx, 'A' yy, null vv, null zz from dual
  5      union all
  6      select 1, null, 'B', null from dual
  7      union all
  8      select 1, null, null, 'C' from dual
  9      union all
 10      select 2, 'D', null, null from dual
 11      union all
 12      select 2, null, 'E', null from dual
 13      union all
 14      select 2, null, null, 'F' from dual
 15      union all
 16      select 3, 'G', null, null from dual
 17      union all
 18      select 3, null, 'H', null from dual
 19      union all
 20      select 3, null, null, 'I' from dual
 21     )
 22  select MAX(t.xx), MAX(t.yy), MAX(t.vv), MAX(t.zz)
 23  from t
 24  group by t.xx;

 MAX(T.XX) M M M
---------- - - -
         1 A B C
         2 D E F
         3 G H I

satyaki>

Regards.

Satyaki De.

N.B.: user573595 has already posted. Again technical problem cause my late...

too late...

null

Thanks Satyaki !
Yes in this way it works....but I noticed that into my discoverer report the situation is a little bit more complicated because I don't have only one value for each field but more as shown below:

 F1          F2          F3          F4
----------- ----------- ----------- -----------
1           A
1           B
1                       C
1                       D
1                       E
1                                   F
2           G
2           I
2           H
2                       T
2                       K
2                                   J

I'd like to have a layout as this:

 F1          F2          F3          F4
----------- ----------- ----------- -----------
    1          A          C          F
    1          B          D
    1                     E                           
    2          G          T          J
    2          I          K
    2          H                             

Practically, I need to group data for each F1 values in order to see the report in this way.
It's like to shift the data up....
I think that is not like a simple pivot query....I tried also to use the analytic functions but....without any relevant result.....

Can you suggest me sometyhing ?

Thanks

Alex

SQL> create table mytable
  2  as
  3  select 1 f1, 'A' f2, null f3, null f4 from dual union all
  4  select 1, 'B', null, null from dual union all
  5  select 1, null, 'C', null from dual union all
  6  select 1, null, 'D', null from dual union all
  7  select 1, null, 'E', null from dual union all
  8  select 1, null, null, 'F' from dual union all
  9  select 2, 'G', null, null from dual union all
 10  select 2, 'I', null, null from dual union all
 11  select 2, 'H', null, null from dual union all
 12  select 2, null, 'T', null from dual union all
 13  select 2, null, 'K', null from dual union all
 14  select 2, null, null, 'J' from dual
 15  /

Tabel is aangemaakt.

SQL> select f1
  2       , max(f2) f2
  3       , max(f3) f3
  4       , max(f4) f4
  5    from ( select t.*
  6                , row_number() over (partition by f1, decode(f2,null,decode(f3,null,3,2),1) order by null) rn
  7             from mytable t
  8         )
  9   group by f1
 10       , rn
 11  /

   F1 F2    F3    F4
----- ----- ----- -----
    1 A     C     F
    1 B     D
    1       E
    2 G     T     J
    2 I     K
    2 H

6 rijen zijn geselecteerd.

Regards,
Rob.

Rob It works perfectly with 4 fields !! Thanks you so much !!
But if I've had, for example 6 fields, two more, filled with the same logic as

  F1   F2   F3   F4   F5   F6

  1    A
  1    B
  1         C
  1         D
  1         E
  1              F
  2    G
  2    I
  2    H
  2         T
  2         K
  2              J
  2                    U
  2                    P
  2                          S

I added values U, P and S

How has to be modified the condition:

row_number() over (partition by f1, decode(f2,null,decode(f3,null,3,2),1) order by null) rn

in order to retrieve

 F1    F2    F3   F4   F5   F6

  1    A     C    F 
  1    B     D
  1          E
  2    G     T    J     U    S
  2    I     K          P
  2    H           

I'm sorry, but I'm quite pressed by the customers....

Dank

Alex

I agree the decode expression is rather cryptic. For six columns you could use this case expression:

case
when f2 is not null then 1
when f3 is not null then 2
when f4 is not null then 3
when f5 is not null then 4
when f6 is not null then 5
end

But I hope you first try to understand the query before delivering it to your customers, because it has to be maintained some day...

Groet,
Rob.

note this will not work if you have more than one not-null column per row

in case there is more than one value per row I could imagine something like

select f1,f2,f3,f4 from (
  select * 
  from mytable
  model
  partition by (f1)
  dimension by (
    row_number() over (partition by f1 order by f2) r2,
    row_number() over (partition by f1 order by f3) r3,
    row_number() over (partition by f1 order by f4) r4,
    row_number() over (partition by f1 order by 1) y
  )
  measures (
    greatest(count(f2) over (partition by f1),count(f3) over (partition by f1),count(f4) over (partition by f1)) x,
    f2,f2 of2, f3,f3 of3,f4, f4 of4)
  rules (
    f2[any,any,any,y]=max(of2)[cv(y),any,any,any],
    f3[any,any,any,y]=max(of3)[any,cv(y),any,any],
    f4[any,any,any,y]=max(of4)[any,any,cv(y),any]
  ) 
)
where y<=x
order by f1,f2,f3,f4;


F1 F2 F3 F4
-- -- -- --
 1 A  C  F 
 1 B  D    
 1    E    
 2 G  K  J 
 2 H  T    
 2 I       

T comes after K here, but there is no way to have K before T , because there is no order key provided by the OP, order by null is not necessarly returning K before T, it may depends from the execution plan, the structure of the table, the indexes, etc...

About the Model clause, I am sure Rob can do better and I am waiting for his solution ;-)

Message was edited by:
Laurent Schneider
is there a way to delete rows in model other than using subquery?

> About the Model clause, I am sure Rob can do better and I am waiting for his solution ;-)

This looks way too complex for the type of question for me. And as said you are generating more rows than necessary that have to be filtered by a subquery in the final step.

Although I'm convinced that the original poster does not have rows with data in more than one of the columns, I could not resist the challenge and came up with this:

SQL> create table mytable
  2  as
  3  select 1 f1, 'A' f2, null f3, null f4 from dual union all
  4  select 1, 'B', null, null from dual union all
  5  select 1, null, 'C', null from dual union all
  6  select 1, null, 'D', null from dual union all
  7  select 1, null, 'E', null from dual union all
  8  select 1, null, null, 'F' from dual union all
  9  select 2, 'G', null, null from dual union all
 10  select 2, 'I', null, null from dual union all
 11  select 2, 'H', null, null from dual union all
 12  select 2, null, 'T', null from dual union all
 13  select 2, null, 'K', null from dual union all
 14  select 2, 'U', null, 'J' from dual
 15  /

Tabel is aangemaakt.

SQL> select f1
  2       , max(decode(l,2,f)) f2
  3       , max(decode(l,3,f)) f3
  4       , max(decode(l,4,f)) f4
  5    from ( select t.*
  6                , row_number() over (partition by f1, l order by f) rn
  7             from ( select f1
  8                         , l
  9                         , decode(l,2,f2,3,f3,4,f4) f
 10                      from mytable
 11                         , (select 2 l from dual union all select 3 from dual union all select 4 from dual)
 12                  ) t
 13            where f is not null
 14         )
 15   group by f1
 16       , rn
 17   order by f1
 18       , rn
 19  /

   F1 F2    F3    F4
----- ----- ----- -----
    1 A     C     F
    1 B     D
    1       E
    2 G     K     J
    2 H     T
    2 I
    2 U

7 rijen zijn geselecteerd.

which I think looks easier.

> Is there a way to delete rows in model other than using subquery?

Not really. You can leverage the "RETURN UPDATED ROWS" clause to not return the original rows, in other words: delete the original rows from the result set. But generating extra cells that are to be deleted later on, generally does not make much sense and (therefore?) there is no way to delete rows in the model clause. So the subquery is the way to do it if you really want to.

Note that I use the subquery technique too for string aggregation using the model clause, because I tested the subquery filter is more performant than the RETURN UPDATED ROWS clause.

Regards,
Rob.

Thanks you so much Rob !!!
I modified my script with your statement

case
when f2 is not null then 1
when f3 is not null then 2
when f4 is not null then 3
when f5 is not null then 4
when f6 is not null then 5
end

.....and it works as I want !!

Alex