sql server - Count and Percentage of skill ratings -

i have program brings in skill ratings assessment people title of "worker” have take along file number assigned. program brings in reporting line each worker part of.

select distinct  o.vp, o.avp, o.director, o.supervisor, o.worker, bs.file_nbr, s.skill bs.score    [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 dataset 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 1 through 5. need do create table shows count , percentage of each rating giving workers each skill grouped vp, avp, supervisor , director. works under avp , wokers under director , 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      , 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 great! thanks!

since have different roles in different columns, compact query, either need dynamic sql or complex pivot. hence, i've opted copy , paste because don't think complexity worth 4 roles have.

i've named query t example.

with roles (     select vp name, 'vp' role, skill, rating t vp not null   union      select avp name, 'avp' role, skill, rating t avp not null   union      select director name, 'director' role, skill, rating t director not null   union      select supervisor name, 'supervisor' role, skill, rating t supervisor not null ), counts (   select name, role, skill       ,count(case when rating = 1 1 else null end) [count of rating 1]       ,count(case when rating = 2 1 else null end) [count of rating 2]       ,count(case when rating = 3 1 else null end) [count of rating 3]       ,count(case when rating = 4 1 else null end) [count of rating 4]       ,count(case when rating = 5 1 else null end) [count of rating 5]       ,count(*) totalcount     roles     group 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))) + '%' [% of rating 1] ,[count of rating 2] ,convert(varchar(10), convert(int,100.0 * [count of rating 2]/nullif(totalcount, 0))) + '%' [% of rating 2] ,[count of rating 3] ,convert(varchar(10), convert(int,100.0 * [count of rating 3]/nullif(totalcount, 0))) + '%' [% of rating 3] ,[count of rating 4] ,convert(varchar(10), convert(int,100.0 * [count of rating 4]/nullif(totalcount, 0))) + '%' [% of rating 4] ,[count of rating 5] ,convert(varchar(10), convert(int,100.0 * [count of rating 5]/nullif(totalcount, 0))) + '%' [% of rating 5] counts order name, skill 

what did here union roles together, hard coding role names. roles reorganizes table has vp gets row vp, has avp gets row avp, .... counts counts workers each name, role, , skill. final select computes percentages.

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