2

I have a problem that I cannot solve. I work on Microsoft SQL Server 2008 and I have a table with four columns 

Id
Date (2013-07, 2013-08, 2011-03, etc)
Amount 1 (100, 150, etc.) 
Amount 2 (100, 80, etc.)

If Amount 1 > 150 then I need to create new columns with the values in Date as column names and distribute Amount 2 into 6 (date) periods starting one month after the Date value.

It should look like this: 

      Id  Date      Amount 1  Amount 2
      ----------------------------------
      1   2013-07    160         60
      2   2013-10    180         80                            

      Id  Date     Amount 1  2013-08  2013-09  2013-10   2013-11   2013-12  2014-01 ...
      --------------------------------------------------------------------------------
      1   2013-07    160         10       10      10         10        10      10
      2   2013-10    180                                     20        20      20...

I don't know how to do this and any help is highly appreciated! Thank you!

user2561907
  • 23
  • 1
  • 1
  • 3

1 Answers1

1

The table itself should not have these additional columns because that would be a denormalized table structure. That's a poor way to store data in many cases. But you can easily do a query against your existing table that will return the additional columns in the form you want, so that you can display it this way. Check out PIVOT and UNPIVOT.

criticalfix
  • 2,870
  • 1
  • 19
  • 32
  • I am actually trying to do a query but I don't know how to dynamically return the column names. I have tried pivot and unpivot but I can't get it to work. Can you please give me an example? Thank you! – user2561907 Jul 24 '13 at 08:06