By-pass fields in SQL 1

[Andrzejek]

If I have a table like this:
[tt]
FieldA FieldB FieldC FieldD
100 ABC Andy Brown
[red]100 ABC[/red] Susie White
[red]100 ABC[/red] Bobby Green
200 XYZ Billy Black
[red]200 XYZ[/red] Duffy Duck
300 JLK Bugs Bunny[/tt]

How can I create a SELECT statement to by-pass all [red]RED[/red] fields that repeat and have them as blanks?

So the output would be like this:
[tt]
FieldA FieldB FieldC FieldD
100 ABC Andy Brown
Susie White
Bobby Green
200 XYZ Billy Black
Duffy Duck
300 JLK Bugs Bunny[/tt]

Have fun.

---- Andy

 

[Dagon]

That's the sort of thing you can do with the SQL*Plus "break on ... noduplicates" command.

break on a nodup
select 1 as a, 1 as b from dual
union all
select 1, 2 from dual;

    A          B
----- ----------
    1          1
               2

For Oracle-related work, contact me through Linked-In.

 

[Andrzejek]

I was hoping to create a recordset for my application. And I don't use SQL Plus :-(


Have fun.

---- Andy

 

[Dagon]

You might be able to do it with something like this:

 create table rollup_test (fielda number, fieldb varchar2(3), fieldc varchar2(10), fieldd varchar2(10));
insert into rollup_test values (100,       'ABC' ,     'Andy',     'Brown');
insert into rollup_test values (100,       'ABC' ,     'Susie',    'White');
insert into rollup_test values (100,       'ABC' ,     'Bobby' ,   'Green');
insert into rollup_test values (200,       'XYZ' ,     'Billy',    'Black');
insert into rollup_test values (200,       'XYZ' ,     'Duffy',    'Duck');
insert into rollup_test values (200,       'ZZZ',   'Duffy',    'Duck');
insert into rollup_test values (300,       'JLK' ,    'Bugs',     'Bunny');

select  (case when substr(comp_key,1,3)  = substr(prev_key,1,3) then null else substr(comp_key,1,3) end) as fielda,
         (case when substr(comp_key,4,3)  = substr(prev_key,4,3) then null else substr(comp_key,4,3) end ) as fieldb,
         fieldc,
         fieldd
from
(
select fielda||fieldb as comp_key, lag(fielda||fieldb) over (order by fielda||fieldb, fieldc, fieldd) as prev_key, fieldc, fieldd from rollup_test
order by fielda||fieldb)

FIELDA FIELDB FIELDC     FIELDD    
------ ------ ---------- ----------
100    ABC    Andy       Brown     
              Bobby      Green     
              Susie      White     
200    XYZ    Billy      Black     
              Duffy      Duck      
       ZZZ    Duffy      Duck      
300    JLK    Bugs       Bunny

It only works if fielda and fieldb are of fixed length. If they're not, you could get round that by padding them out in the comp_key/prev_key expressions

For Oracle-related work, contact me through Linked-In.

 

[LKBrwnDBA]

Or maybe like this:

SQL> WITH rollup_test
  2    AS (
  3       SELECT 100 flda, 'ABC' fldb, 'Andy' fldc, 'Brown' fldd FROM dual UNION
  4       SELECT 100,      'ABC',     'Susie',      'White'      FROM dual UNION
  5       SELECT 100,      'ABC',     'Bobby',      'Green'      FROM dual UNION
  6       SELECT 200,      'XYZ',     'Billy',      'Black'      FROM dual UNION
  7       SELECT 200,      'XYZ',     'Duffy',       'Duck'      FROM dual UNION
  8       SELECT 200,      'ZZZ',     'Duffy',       'Duck'      FROM dual UNION
  9       SELECT 300,      'JLK',      'Bugs',      'Bunny'      FROM dual)
 10  -- -
 11  SELECT DECODE (ra, 1, flda, NULL) flda,
 12         DECODE (rb, 1, fldb, NULL) fldb,
 13         fldc,
 14         fldd
 15    FROM (SELECT flda, fldb, fldc, fldd
 16               , ROW_NUMBER () OVER (PARTITION BY flda ORDER BY fldb) ra
 17               , ROW_NUMBER () OVER (PARTITION BY flda, fldb
 18                                         ORDER BY Fldc, fldd)  rb
 19            FROM rollup_test)
 20  /

      FLDA FLD FLDC  FLDD
---------- --- ----- -----
       100 ABC Andy  Brown
               Bobby Green
               Susie White
       200 XYZ Billy Black
               Duffy Duck
           ZZZ Duffy Duck
       300 JLK Bugs  Bunny

7 rows selected.

SQL>

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Dagon]

That looks much better. Mine was a bit of a hurried effort before I left for home last night.

For Oracle-related work, contact me through Linked-In.