格式化SQL Server数据表数据以进行报告

ccrfmcuu 于 2022-11-28 发布在 SQL Server

关注(0)|答案(3)|浏览(141)

我有一个表，列如下-

有行显示不同项目下的不同人员的分配。月份（列）可以扩展到12月20日，并从1月21日继续以上述相同的模式。
一个员工可以在给定月份中标记到任意数量的项目。
现在，为了准备一份关于这一点的报告，我想将数据格式化如下-

因此，基本上对于每个项目，一个工作人员被分配到，我想复制每年的12个月，并显示指定的分配。
包含数据的表的名称为[Staff Allocation]，它具有以下字段- [Staff ID]、[Project ID]、[Jan，20]、[Feb，20]、[Mar，20]、[Apr，20]等等，如上图所示。
有没有办法做到这一点？
任何帮助都是非常感谢的。
添加以下示例数据-
| 员工ID|项目编号|1月20日|二月二十日|三月二十日|4月20日|五月二十日|六月二十日|7月20日|
| - -|- -|- -|- -|- -|- -|- -|- -|- -|
| 一个|二十个|第0页|第0页|第0页|100个|八十|10个|第0页|
| 一个|三十|第0页|第0页|第0页|第0页|二十个|九十|100个|
| 2个|二十个|100个|100个|100个|第0页|第0页|第0页|第0页|
| 三个|五十个|八十|100个|第0页|第0页|第0页|第0页|第0页|
| 三个|六十|十五|第0页|第0页|第0页|二十个|第0页|第0页|
| 三个|七十|五个|第0页|100个|100个|八十|第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

sql-server

来源：https://stackoverflow.com/questions/74589437/formatting-sql-server-table-data-for-reporting

3条答案

按热度按时间

qyyhg6bp1#

下面是一个示例，说明如何使用 cross apply 和一个 values 表构造函数将列反透视为行。

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

赞(0）回复(0）举报 2022-11-28

llew8vvj2#

您需要Unpivot和，因为您的列名格式为MMMYY。您可以使用Right和Left函数，如下所示：

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

赞(0）回复(0）举报 2022-11-28

jq6vz3qz3#

我感觉（通过列名）源表会随着时间的推移而扩展。
下面是一个选项，它可以动态地取消透视数据，而无需实际使用动态SQL

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

结果

赞(0）回复(0）举报 2022-11-28

我来回答

格式化SQL Server数据表数据以进行报告

3条答案

相关问题

热门标签

最新问答