Count and Percentage of skill ratings

Question

I have a program which brings in Skill ratings for an assessment people with the title of "Worker” have to take along with a file number they are assigned. The program also brings in the reporting line that each worker is a part of.

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; 

I get a dataset that could look like this:

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

The rating levels are 1 through 5. What I need to do is to do is create a table that shows the count and percentage of each rating giving to the workers for each skill grouped by Vp, AVP, Supervisor and Director. So all the works that are Ultimately under a AVP and all the wokers that are Ultimately under a director and so on.

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              

Any assistance would be great! Thanks!

Answer 1

Since you have your different roles in different columns, to get a compact query, you either need dynamic sql or a complex pivot. Hence, I've opted for just copy and paste because I don't think the complexity is worth the 4 roles you have.

I've named your query T for the example.

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

What I did here is to union all the roles together, hard coding the role names. roles reorganizes the table so that everyone who has a VP gets a row with that VP, everyone who has an AVP gets a row with that AVP, .... counts then counts all the workers for each name, role, and skill. The final select computes the percentages.

Here's a fiddle showing it in action: http://sqlfiddle.com/#!3/fe09d/15