sql server - Get Other Column to Include on Pivot Calculation SQL -
i have following table:
-------------------------------------------------------------- inspectyear part cpoint pos1 pos2 pos3 pos4 -------------------------------------------------------------- 2009 001 0.2 8 8 9 7 2009 002 0.2 9 7 8 6 2011 001 0.3 9 9 8 7 2011 002 0.7 7 8 6 8 2013 001 0.2 8 9 7 9 2013 002 0.4 7 7 8 8 2015 001 0.2 8 8 7 4 2015 002 0.8 7 6 9 8 then have following query:
declare @inspectyear nvarchar(max), @calc nvarchar(max), @query nvarchar(max); set @inspectyear = stuff((select distinct ',' + quotename(inspectyear) ##t2 c xml path(''), type).value('.','nvarchar(max)'),1,1,'') select @calc = ', (' + quotename(max(inspectyear)) + ' - ' + quotename(max(inspectyear)-2) + ') / 2.0 calc1, 1.0 * (' + quotename(max(inspectyear)) + ' - ' + quotename(min(inspectyear)) + ') / (' + cast(max(inspectyear) char(4)) + '-' + cast(min(inspectyear) char(4)) + ') calc2' #t2; set @query = ';with data ( select inspectyear, cpoint, partno, pos, number #t2 unpivot ( number pos in ([pos1], [pos2], [pos3], [pos4]) ) unpvt ) select * ' + @calc + ' ##temp data pivot ( sum(number) inspectyear in (' + @inspectyear + ') ) pvt order partno'; exec sp_executesqk @query = @query; select * ##temp; drop table ##temp; the result should be:
---------------------------------------------------------------------------- part cpoint pos 2009 2011 2013 2015 calc1 calc2 ---------------------------------------------------------------------------- 001 0.2 pos1 8 9 8 10 1 0.33 001 0.3 pos2 8 9 9 8 -0.5 0 001 0.2 pos3 9 8 7 7 0 -0.33 001 0.2 pos4 7 7 9 4 -2.5 -0.5 my question is, how can add new calculation newest value minus cpoint value...? let say, calc3.
for example, on first record on result above.
---------------------------------------------------------------------------------- part cpoint pos 2009 2011 2013 2015 calc1 calc2 calc3 ---------------------------------------------------------------------------------- 001 0.2 pos1 8 9 8 10 1 0.33 9.8 10 value on newest year column minus 0.2 cpoint. result calc3 = 9.8
does have idea this? thank you.
Comments
Post a Comment