Assign next aviliable column values

• abhas

  SSCarpal Tunnel

  Points: 4681

  More actions

  September 29, 2014 at 6:27 am

  #293722

  Hi Friends,

  Please find below object and data:

  create table #StuDetails(City varchar(25),StuStatus varchar(25), currentValue int,Week1 int,week2 int,week3 int,week4 int)

  insert into #StuDetails values('A','new',13,10,0,0,12)

  insert into #StuDetails values('B','Old',10,10,41,0,12)

  insert into #StuDetails values('C','Fail',10,9,0,0,5)

  select * from #StuDetails

  Output of above is display as:

  CityStuStatuscurrentValueWeek1week2week3week4

  Anew 13 10 0 0 12

  BOld 10 10 41 0 12

  CFail 10 9 0 0 5

  Now for columns Week1 to week3 if value is 0 then i want to display by searching next week value, if it is also 0 then go for next week and if value found there then display instead of zero. so my output would be as below instead of above.

  CityStuStatuscurrentValueWeek1week2week3week4

  Anew 13 10 12 12 12

  BOld 10 10 41 12 12

  CFail 10 9 5 5 5

  Please help.

  Thanks

  Abhas.

• serg-52

  SSCrazy Eights

  Points: 9913

  More actions

  September 29, 2014 at 7:28 am

  #1748807

  Plain CASE will help

  create table #StuDetails(City varchar(25),StuStatus varchar(25), currentValue int,Week1 int,week2 int,week3 int,week4 int)

  insert into #StuDetails values('A','new',13,10,0,0,12)

  insert into #StuDetails values('B','Old',10,10,41,0,12)

  insert into #StuDetails values('C','Fail',10,9,0,0,5)

  SELECT City, StuStatus, currentValue

  ,Week1 = CASE week1 WHEN 0 THEN

  CASE week2 WHEN 0 THEN

  CASE week3 WHEN 0 THEN week4 ELSE week3 END

  ELSE week2 END

  ELSE week1 END

  ,week2 = CASE week2 WHEN 0 THEN

  CASE week3 WHEN 0 THEN week4 ELSE week3 END

  ELSE week2 END

  ,week3 = CASE week3 WHEN 0 THEN week4 ELSE week3 END

  ,week4

  FROM #StuDetails

• CKX

  SSCrazy

  Points: 2519

  More actions

  October 2, 2014 at 8:58 am

  #1749703

  Maybe...

  SELECTCity,

  StuStatus,

  CurrentValue,

  COALESCE(NULLIF(week1,0), NULLIF(week2,0), NULLIF(week3,0), NULLIF(week4,0)) AS Week1,

  COALESCE(NULLIF(week2,0), NULLIF(week3,0), NULLIF(week4,0)) AS Week2,

  COALESCE(NULLIF(week3,0), NULLIF(week4,0)) AS Week3,

  Week4

  FROM #StuDetails;