Monday, April 28, 2014

Use first row of table to determine calculation of rest of the rows of that table.


create table test (Name varchar(20),[01] int,[02] int,[03] int,[04] int,[05] int,[06] int,[07] int,[08] int,[09] int)
insert into test values('Emp 1',8,8,8,8,8,0,0,8,8),('Emp 2',8,7,8,9,8,0,0,8,7),('Emp 3',6,8,8,8,8,0,0,8,8)


;with mycte as(
select name, val,col from (select * from test) src
cross apply (values([01],'01'),([02],'02'),([03],'03'),([04],'04'),([05],'05'),([06],'06'),([07],'07'),([08],'08'),([09],'09')) d(val,col)
)

Select name,[01],[02],[03],[04],[05],[06],[07],[08],[09] from
(select name, Case when col='04' then val*2 else val end val,col from mycte) src
PIVOT (max(val) For col In ([01],[02],[03],[04],[05],[06],[07],[08],[09])) pvt





drop table test


No comments:

Post a Comment