table needs change

General
Enthusiast

table needs change

Hi, I have table like below: All i need is if  roll# has flag in mutiple columns highest heirarcy column should show the flag and rest columns should be null 

for eg:

125 has flag is both auto & small = i want only small flag to be shows and auto flag to be null becasue small is higher in heirarchy than auto

another eg:

for 456 auto,high and exec flags are there. i want only exec flag to be show and reset ofthe ccoumns should be null.

I need to create a new table based on this.

Can you please advice. 




roll# auto small med large exec
121 1 0 0 0 0
125 1 1 0 0 0
136 0 1 0 1 1
1456 0 0 1 0 0
456 1 0 0 1 1
457 0 0 0 0 1
876 0 0 0 1 0
4554 0 1 0 0 0
3 REPLIES
Enthusiast

Re: table needs change




roll# auto small med large exec
121 1 0 0 0 0
125 1 1 0 0 0
136 0 1 0 1 1
1456 0 0 1 0 0
456 1 0 0 1 1
457 0 0 0 0 1
876 0 0 0 1 0
4554 0 1 0 0 0
Enthusiast

Re: table needs change

roll     auto    sm   med     large    exec

121     1         0     0          0          0

456     1         0      0         1           1

1456   0         0      1         0           0

Enthusiast

Re: table needs change

This can be done in multiple ways. Below is one way:

SEL ROLL
, CASE WHEN (EXECF =1 OR LARGEF = 1 OR MED = 1 OR SM = 1) THEN NULL ELSE AUTOF END AS AUTOF
, CASE WHEN (EXECF =1 OR LARGEF = 1 OR MED = 1) THEN NULL ELSE SM END AS SM
, CASE WHEN (EXECF =1 OR LARGEF = 1) THEN NULL ELSE MED END AS MED
, CASE WHEN EXECF =1 THEN NULL ELSE LARGEF END AS LARGEF
, EXECF
FROM <YOUR_TABLE>