CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012
Ajit Kumar
Ajit Kumar

Reputation: 107

sql server complex query

SQL SERVER

1st one is the source table and 2nd one is the destination table, so in 2nd table by using unpivot the the 12 month columns converted into single column and YTD field should be update automatically. If the month name is Jan means the YTD field will Jan data only, if it is Feb means it will add the Jan data with Feb data. same if it is Dec means it will add from Jan to Dec of the same row.

Upvotes: 0

Views: 223

Answers (3)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

Looks like what you require is to do unpivot as below:

Select *, [YTD] = sum(yourYTD calculation column) over(partition by [OruCode],[year] order by Month) from (
   Select OruCode, BGName ---your required columns upto dec
       from table1 t1 cross join table2 t2
 )
 unpivot (KPINo for [Month] in ([Jan],[Feb]...) ) p

Upvotes: 0

SqlZim
SqlZim

Reputation: 38023

Using cross apply(values ...) to unpivot your data: 

select 
    t.[oru code]
  , t.[bg name]
  , t.[bu name]
  , t.[domain]
  , t.KPI
  , t.UoM
  , t.[Year]
  , t.[ACT/TARGET]
  , v.[Month]
  , v.KPI
  , t.[YTD/ITM]
from t
  cross apply (values 
    ('Jan',Jan) ,('Feb',Feb) ,('Mar',Mar)
   ,('April',April) ,('May',May) ,('Jun',Jun)
   ,('Jul',Jul) ,('Aug',Aug) ,('Sep',Sep)
   ,('Oct',Oct) ,('Nov',Nov) ,('Dec',Dec)
    ) v ([Month],KPI)

Upvotes: 1

Daniel
Daniel

Reputation: 426

Just select all your Month indivitually and then UNION them:

select [ORU code], [BG Name], YEAR, 'Jan' as [Month], [Jan] as [KPI #], [Jan] 
as [YTD/ITM] from  source_table 
union all
select [ORU code], [BG Name], YEAR, 'Feb' as [Month], [Feb] as [KPI #], [Jan]+
[Feb]  as [YTD/ITM] from  source_table 
union all
select ....

you get the Idea

Upvotes: 0

Related Questions