CODEWITHSUNDEEP
t-sql
Anil Soman
Anil Soman

Reputation: 2467

Changing position of data in columns depending on actual data

Suppose I have a table like below

ID  Marks1  Marks2  Marks3
-------------------------
1   10      0       4
2   0      40       90

Now, I need to select from this table in a way that will give precedence to positive values first. So if the marks are 0 then it will be shifted to right. The SELECT should give following output

ID  Marks1  Marks2  Marks3
-------------------------
1   10      4       0
2   40      90      0

Can you please guide me for the approach? It will be great if it can be done in a select statement itself. Thanks in advance.

Upvotes: 0

Views: 34

Answers (1)

u07ch
u07ch

Reputation: 13702

Something like this you will need to check for each subsequent row that the previous column isn't 0. Have selected the values out as null as it makes the code slightly easier to read as i can use coalesce

Select
Coalesce(Marks1, Marks2, Marks3,0) as Marks1,
Case when marks1 is not null 
     then Coalesce(Marks2, Marks3, 0) else 0 
end as Marks2,
case when marks1 is not null 
     and marks2 is not null 
     then Coalesce(Marks3,0) 
end as Marks3

from
(
Select
Case when Marks1 =0 then null else Marks1 end as Marks1,
Case when Marks2 =0 then null else Marks2 end as Marks2,
Case when Marks3 =0 then null else Marks3 end as Marks3
  From mytbl
)

Upvotes: 1

Related Questions