CODEWITHSUNDEEP
sqlsql-serverreporting-services
Chris
Chris

Reputation: 93

Formatting SQL Server table data for reporting

I have a table with the columns as below -

enter image description here

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 -

enter image description here

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 -

Staff ID Project ID Jan,20 Feb,20 Mar,20 Apr,20 May,20 Jun,20 Jul,20
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 
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

Upvotes: 1

Views: 122

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 82020

I get the sense (by your column names) that the source table will expand over time.

Here is an option that will dynamically unpivot your data without actually using Dynamic SQL

Example or dbFiddle

Select A.[Staff ID]
      ,A.[Project ID]
      ,[Month]    = left([Key],3)
      ,[Year]     = '20'+right([Key],2)
      ,Allocation = try_convert(int,B.[value])
 From  YourTable A
 Cross Apply  (
                 Select [Key]
                       ,[Value]
                  From  OpenJson(  (Select A.* For JSON Path,Without_Array_Wrapper )  ) 
                  Where [Key] not in ('Staff ID','Project ID')
              ) B

Results

enter image description here

Upvotes: 2

RF1991
RF1991

Reputation: 2265

you need Unpivot and since your column names are in format of MMMYY.you can use Right and Left Functions as follows:

select [StaffID]
      ,[ProjectID]
      ,left(indicatorname,3) Month
    ,concat('20',right(indicatorname,2)) Year
  ,Allocation
from test
unpivot
(
  Allocation
  for indicatorname in ([Jan20]
      ,[Feb20]
      ,[Mar20]
      ,[Apr20]
      ,[May20]
      ,[Jun20]
      ,[Jul20]
      )
) unpiv;

dbfiddle

Upvotes: 2

Stu
Stu

Reputation: 32614

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);

Demo DB<>Fiddle

Upvotes: 2

Related Questions