Reputation: 93
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 -
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
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
Upvotes: 2
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;
Upvotes: 2
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);
Upvotes: 2