I have a table with the columns as below -

There are rows showing the allocation of various people under various project. The months (columns) can extend to Dec,20 and continue on from Jan,21 in the same pattern as above.

One Staff can be tagged to any number of projects in a given month.

Now to prepare a report on this I would like to format the data as below -

So basically for each project that a staff is assigned to, I would like to duplicate each of the 12 months for each year and show the designated allocation.

The name of the table containing the data is [Staff Allocation] and it has the following fields - [Staff ID],[Project ID],[Jan,20],[Feb,20],[Mar,20],[Apr,20] and so on as per the image above.

Is there any way to do this?

Any help on this is highly appreciated.

Adding in the sample data as below -

create table test(StaffID int,  ProjectID int,  Jan20 int,  Feb20 int,  Mar20 int,  Apr20 int,  May20 int,  Jun20 int,  Jul20 int)

insert into test values 
  (1,20,0,0,0,100,80,10,0),
  (1,30,0,0,0,0,20,90,100),
  (2,20,100,100,100,0,0,0,0),
  (3,50,80,100,0,0,0,0,0),
  (3,60,15,0,0,0,20,0,0),
  (3,70,5,0,100,100,80,0,0)

Select * from test

CodePudding user response：

Here is an example of how you can unpivot your columns into rows by using cross apply and a values table constructor.

select StaffId, ProjectId, v.*
from t
cross apply(values
  ('Jan', 2020, Jan20),
  ('Feb', 2020, Feb20),
  ('Mar', 2020, Mar20),
  ('Apr', 2020, Apr20),
  ('May', 2020, May20),
  ('Jun', 2020, Jun20),
  ('Jul', 2020, Jul20)
)v([Month], [Year], Allocation);