技能等级的计数和百分比

Question

我有一个程序，它为具有“工人”头衔的人带来了评估的技能评级，以及分配给他们的文件号。该程序还引入了每个工人都是其中一部分的报告线。

SELECT distinct 
o.VP,
o.AVP,
o.Director,
o.Supervisor,
o.Worker,
bs.File_NBR,
s.Skill
bs.score
FROM    [New_EEs].[dbo].[SBC_Best_Scores] bs
inner join new_ees.dbo.SBC_Skills s
on   bs.Skill_NBR=s.SKILL_NBR
inner join gw_PPP.dbo.Org_Hierarchy oon 
bs.File_NBR=o.File_NBR; 

我得到的数据集可能如下所示：

VP  AVP Director    Supervisor  Worker  File_NBR    Skill   Rating
Gerald  Kris    Doris   NULL    Mack    107812  B2  4
Gerald  Kris    Doris   NULL    Mack    107812  D1  3
Gerald  Kris    Doris   NULL    Mack    107812  D2  3
Gerald  Kris    Doris   NULL    Mack    107812  D3  3
Gerald  Kris    Doris   NULL    Mack    107812  E1  4
Gerald  Kris    Mike    NULL    Brady   109080  A1  5
Gerald  Kris    Mike    NULL    Brady   109080  B1  4
Gerald  Kris    Mike    NULL    Brady   109080  B2  3
Gerald  Kris    Mike    NULL    Brady   109080  B3  4
Gerald  Kris    Mike    NULL    Brady   109080  C1  4
Gerald  Kris    Mike    NULL    Brady   109080  C2  4
Gerald  Kris    Mike    NULL    Brady   109080  C3  0
Kim Harry   NULL    Grant   Tom 108457  B1  4
Kim Harry   NULL    Grant   Tom 108457  B2  4
Kim Harry   NULL    Grant   Tom 108457  C1  4
Kim Harry   NULL    Grant   Tom 108457  C2: 5
Kim Harry   NULL    Grant   Tom 108457  C5  5
Kim Harry   NULL    Grant   Tom 108457  D1  4
Kim Harry   NULL    Grant   Tom 108457  D2  5
Kim Harry   NULL    Grant   Tom 108457  D3  4
Kim Harry   NULL    Grant   Jean    106934  C5  4
Kim Harry   NULL    Grant   Jean    106934  D1  5
Kim Harry   NULL    Grant   Jean    106934  D3  5
Kim Harry   NULL    Grant   Raphe   108901  B2  5
Kim Harry   NULL    Grant   Raphe   108901  C2  5
Kim Harry   NULL    Grant   Raphe   108901  C3  4
Kim Harry   NULL    Grant   Raphe   108901  C5  5
Kim Harry   NULL    Grant   Raphe   108901  D2  5
Kim Harry   NULL    Grant   Raphe   108901  E1  5
Kim Harry   NULL    Grant   Tyika   107923  B1  5
Kim Harry   NULL    Grant   Tyika   107923  B2  5
Kim Harry   NULL    Grant   Tyika   107923  D2  4
Kim Harry   NULL    Grant   Tyika   107923  D3  4

评级级别是1到5。我需要做的是创建一个表，显示每个技能的计数和百分比给予工人的每个技能分组的副总裁，AVP，主管和主任。所以，所有最终在AVP之下的作品，以及所有最终在导演之下的作品，等等。

Name    Role    Skill   Count of    % of    Count of      % of  
                              Rating 1   Rating 1  Rating 2   Rating 2
Gerald  VP  A1  100 29% 130 33%
Gerald  VP  B1  95  28% 95  24%
Gerald  VP  B2  120 35% 70  18%
Gerald  VP  B3  30  9%  100 25%
Kim VP  A1              
Kim VP  B1              
Kim VP  B2      and so on       
Kim VP  B3              
Kris    AVP A1              
Kris    AVP B1              
Kris    AVP B2              
Kris    AVP B3              
Harry   AVP A1              
Harry   AVP B1              
Harry   AVP B2              
Harry   AVP B3              
Doris   Director    A1              
Doris   Director    B1              
Doris   Director    B2              
Doris   Director    B3              
Mike    Director    A1              
Mike    Director    B1              
Mike    Director    B2              
Mike    Director    B3              
Grant   Supervisor  A1              
Grant   Supervisor  B1              
Grant   Supervisor  B2              
Grant   Supervisor  B3              

任何帮助都是很棒的！谢谢!

Answer 1

因为您在不同的列中有不同的角色，所以要获得紧凑查询，您要么需要动态sql，要么需要复杂的pivot。因此，我选择了只复制和粘贴，因为我认为复杂性不值得你拥有4个角色。

我已经将您的查询命名为T作为示例。

with roles as (
    select VP as Name, 'VP' as Role, Skill, Rating from t where VP is not null
  union all 
    select AVP as Name, 'AVP' as Role, Skill, Rating from t where AVP is not null
  union all 
    select Director as Name, 'Director' as Role, Skill, Rating from t where Director is not null
  union all 
    select Supervisor as Name, 'Supervisor' as Role, Skill, Rating from t where Supervisor is not null
), counts as (
  select Name, Role, Skill
      ,count(case when rating = 1 then 1 else NULL end) as [Count of Rating 1]
      ,count(case when rating = 2 then 1 else NULL end) as [Count of Rating 2]
      ,count(case when rating = 3 then 1 else NULL end) as [Count of Rating 3]
      ,count(case when rating = 4 then 1 else NULL end) as [Count of Rating 4]
      ,count(case when rating = 5 then 1 else NULL end) as [Count of Rating 5]
      ,count(*) as TotalCount
    from roles
    group by Name, Role, skill
)
select Name, Role, Skill
,[Count of Rating 1]
,CONVERT(varchar(10), convert(int,100.0 * [Count of Rating 1]/NULLIF(TotalCount, 0))) + '%' as [% of Rating 1]
,[Count of Rating 2]
,CONVERT(varchar(10), convert(int,100.0 * [Count of Rating 2]/NULLIF(TotalCount, 0))) + '%' as [% of Rating 2]
,[Count of Rating 3]
,CONVERT(varchar(10), convert(int,100.0 * [Count of Rating 3]/NULLIF(TotalCount, 0))) + '%' as [% of Rating 3]
,[Count of Rating 4]
,CONVERT(varchar(10), convert(int,100.0 * [Count of Rating 4]/NULLIF(TotalCount, 0))) + '%' as [% of Rating 4]
,[Count of Rating 5]
,CONVERT(varchar(10), convert(int,100.0 * [Count of Rating 5]/NULLIF(TotalCount, 0))) + '%' as [% of Rating 5]
from counts
order by Name, skill

我在这里所做的是将所有角色联合在一起，对角色名称进行硬编码。roles会重新组织表，这样每个拥有VP的人都会与该VP排一行，每个拥有一个AVP的人都会与该AVP排一排……然后，counts对每个名称、角色和技能的所有工作人员进行计数。最后一次选择将计算百分比。

这里有一个小提琴展示了它的实际效果：http://sqlfiddle.com/#!3/fe09d/15