您的位置: 首页  >  技术问答  >  如何在SQL Server中使用自定义PIVOT？如何在SQL服务器中使用PIVOT运算符显示之间和之间的周数？

如何在SQL Server中使用自定义PIVOT？如何在SQL服务器中使用PIVOT运算符显示之间和之间的周数？

分类: 技术问答 • 2022-04-23 21:18:10
问题描述：

我有一个表tblEmployeeDetails，其中保存员工详细信息。



I have one table "tblEmployeeDetails" where employee details are saved.

Emp ID  Emp-Name Age  Date_of_Joining
-------------------------------------
E001    Mahesh   25   18-01-2016
E004    Suresh   29   13-01-2016 
E002    Kumar    23   10-01-2016
E003    Raj      27   26-01-2016





从日期搜索为'01 -01-2016'，日期为31-01-2016。汇总结果将在输出中显示为：





when search from date as '01-01-2016' and to date as 31-01-2016.The summary result will show in the Output as :

[01-2016]  [02-2016]  [03-2016] [04-2016] [05-2016]
----------------------------------------------------
   0           2          1        1         0
----------------------------------------------------





在输出中，标题显示[Week No - Year]，

从日期和日期开始，（2016年1月1日至2016年1月31日） - 有5周的时间。

因此在输出中它显示为5周一年。

in第一周，没有员工加入。

在第二周，2名员工加入。

在第3周，1名员工加入。

在第4周，1名员工加入。

在第5周，没有员工加入。



i不知道如何显示此输出..

请帮助..

提前致谢...



问候

Mahesh



我尝试过：





in the output, the header is showing [Week No - Year ],
from date and to date ,(1-jan-2016 to 31-Jan-2016) - 5 weeks are there.
so in the output it is showing 5 weeks with year.
in the 1st week, no employees have joined.
in the 2nd week , 2 employees have joined.
in the 3rd week , 1 employee has joined.
in the 4th week, 1 employee has joined.
in the 5th week, no employees has joined.

i don't know how to show this output..
Please help..
Thanks in advance...

Regards
Mahesh

What I have tried:

Emp ID  Emp-Name Age  Date_of_Joining
-------------------------------------
E001    Mahesh   25   18-01-2016
E004    Suresh   29   13-01-2016 
E002    Kumar    23   10-01-2016
E003    Raj      27   26-01-2016

答

Quote：

先生，我不知道该怎么做？

sir i don't know what to do in this ?



首先将数据转换为汇总形式：


So start by getting the data into a aggregated form:

SELECT DatePart(ww, Date_of_Joining) AS WeekNo, COUNT(*) AS PerMonth 
FROM tblEmployeeDetails 
GROUP BY DatePart(ww, Date_of_Joining)

这为您提供了所需的信息，没有归零信息，也没有PIVOT。

接下来，添加零信息：这很复杂，最简单的方法是创建一个包含所有周数的表：一列，包含1到53个。

This gives you the info you need, without the zeroed info, and without the PIVOT.
Next, add the zero information: That's complex, and the simplest way to do it is to create a table of all the week numbers: a single column, containing 1 to 53 inclusive.

SELECT w.WeekNo AS WeekNo, COUNT(s.EMPId) AS PerMonth 
FROM tblEmployeeDetails s
RIGHT JOIN dbo.WeekNumbers w ON w.WeekNo = DatePart(ww, s.Date_of_Joining)
WHERE w.WeekNo BETWEEN 1 AND 5
GROUP BY w.WeekNo



现在，您拥有所需的原始数据：


Now, you have the raw data you need:

WeekNo	PerMonth
1	0
2	0
3	2
4	1
5	1

你剩下要做的就是PIVOT了。我会留给你的！ ：笑：

And all you have left to do is PIVOT that. I'll leave that to you! :laugh:


我会说你在将PIVOT与SQL Server一起使用 [ ^ ]



我希望这对你有用
I'd say you have an almost identical case in the example 3 at Using PIVOT with SQL Server[^]

I hope that is useful for you


相关推荐