I want to create a tabular data report that resembles the tabular layout of an excel pivot table where item labels are not repeated - so I don’t want like data to be repeated row to row. I definitely want to use the Toad data report because I am able to easily embed a file path in one of my columns. However, when I group items, even if I group all of them in the same header group, all of the grouped items appear 1 row above the rest of the data. How do I group items so that like data appears like the excel tabular pivot table?
This is how I want data to appear.
You can accomplish something similar using analytic functions.
SQL> select c1, c2, c3, c4 from t order by c1, c2, c3, c4 ;
C1 C2 C3 C4
a d g s
a d h t
a d i u
a d j v
b e k w
b e k x
b e l y
c f m a
c f m b
c f m z
c f n c
c f o d
c f p e
c f q f
c f r g
15 ligne(s) sélectionnée(s).
SQL> select
2 case c1_counter when 1 then c1 else null end as new_c1,
3 case c2_counter when 1 then c2 else null end as new_c2,
4 case c3_counter when 1 then c3 else null end as new_c3,
5 c4
6 from
7 (select
8 c1, row_number() over (partition by c1 order by c1) as c1_counter,
9 c2, row_number() over (partition by c1, c2 order by c1, c2) as c2_counter,
10 c3, row_number() over (partition by c1, c2, c3 order by c1, c2, c3) as c3_counter,
11 c4
12 from t
13 )
14 order by c1, c2, c3, c4 ;
NEW_C1 NEW_C2 NEW_C3 C4
a d g s
h t
i u
j v
b e k w
x
l y
c f m a
b
z
n c
o d
p e
q f
r g
15 ligne(s) sélectionnée(s).