sql server - Group by Clause -

i have following tables issue, issuestatus , customtable(ct) .following data in same

issue:                       issuestatus:                    ct  issue|pkey|issuestatus       id | pname                     issue|referred ---------------------------------------------------------------------------- 100     t-1  1                  1   open                       100     null 200     t-2  2                  2   closed                     200     null  300     t-3  3                  3   acknowledged               700     dev 400     t-4  4                  4   in progress                800     qa 500     t-5  1                  5   referred                   800     null  600     t-6  2                                                 400     null 700     t-7  5                                                 500     null    800     t-8  5                                                 700     null 

i need output following

pname      |  count open           2 closed         2 acknowledged   1 in progress    1 dev            1  qa             1 

hence if see, need group issuestatus , count of pkey, when issuestatus "referred", need in ct table , corresponding referred column text in group by. issue in ct linked issue in issue table.

this had tried , not right output

select pname = case      when pname='referred' ct.referred      else pname end,     count(pkey)   ct,issue a,issuestatus a.issuestatus=issuestatus.id , a.issue=ct.issue  group pname,ct.referred 

i tested following query giving want

try

with t1 (   select (case when pname='referred' referred else pname end)as [pn]   (select i.issue,c.referred,iss.pname         issue         inner join issuestatus iss on i.issuestatus=iss.id         left outer join ct c on c.issue=i.issue        ) t )   select pn,count(*) cnt t1 pn not null group pn order cnt desc 

here sqlfiddle