CODEWITHSUNDEEP
sql-server
LeO
LeO

Reputation: 35

Dynamically add column values in SQL Server

I have following data in table.

State   P_Name     Product    Apr-2013  May-2013   Jun-2013   July-2013   Aug-2013   Sep-2013   Oct-2013   Nov-2013   Dec-2013   Jan-2014   Feb-2014   Mar-2014
===============================================================================================================================================================
s1      p1         pro1        1500      4587        5245      6541        9683       45878      65464      4444       4411       21452       223322    232322
s1      p1         pro2        1500      4587        5245      6541        9683       45878      65464      4444       4411       21452       223322    232322

Now first three columns are static but values are dynamic and remaining column_names and values are dynamic. With this there is a variable

Declare @MONTHS INT
SET @MONTHS = 1--3/6/12 can be 1/3/6/12 as monthly/quater/half-year/fullyear

Now depending on values of @MONTHS i have to add values to first/first-to-third/first-to-six/first-to-twelve column and display only that columns. IF

SET @MONTHS = 1

then above data is result. but

SET @MONTHS = 3

then the desired result is as follows:

State   P_Name     Product    Jun-2013   Sep-2013   Dec-2013   Mar-2014
=======================================================================
s1      p1         pro1        11332      62102      74319       477096
s1      p1         pro2        11332      62102      74319       477096

Here only 3rd month column is displayed with addition of apr-2013,may-2013 and jun-2013 column values( in jun-2013 column), same i want for 6 and 12 also in 6th and 12th column. The no of month-columns and no of rows are dynamic. The no of month-columns can be in multiple of 1/3/6/12 only. Need help in this. Thanks.

EDIT: Above table data is derived with another table with help of PIVOT. Is it possible before/with PIVOT without loop..? Below is data before PIVOT.

state   p_name   product   amount   monthnames
================================================
s1      p1       pro1      1500     apr-2013
s1      p1       pro1      4587     may-2013
s1      p1       pro1      5245     jun-2013
and so on................
........................

Upvotes: 1

Views: 1771

Answers (1)

Sean
Sean

Reputation: 1474

Think this will do it. Adding a column name for the derived columns might be a little tricky, but I think it's possible. Also needs some exception handling if the column count is not a multiple of @month. I called my table States.

declare @month int
set @month = 3

declare cur cursor for 
select name
  from syscolumns
 where object_name(id) = 'States'
   and colorder > 3
 order by colorder

declare @count int, @col varchar(100), @sql varchar(max), @tmp varchar(max)

set @count = 1
set @sql = 'select State, P_Name, Product, 0'
set @tmp = ''

open cur
fetch cur into @col
while @@FETCH_STATUS = 0 begin
    set @tmp = @tmp + '+' + @col
    if @count = @month begin
        set @sql = @sql + @tmp + ', 0'
        set @tmp = ''
        set @count = 0
    end

    set @count = @count + 1

    fetch cur into @col
end

set @sql = substring(@sql, 1, len(@sql) - 3) + ' from States'

exec(@sql)

close cur
deallocate cur

Upvotes: 1

Related Questions