problem in writing a sql query

Question

Getting a problem in writing an sql query. two tables:

1st: created patient table                        2nd: already created doc table
     patientid  patientname  docid  workstatus          docid  docname
      1          aaa          2          10               1      ggg
      2          bbb          2          20               2      hhh
      3          ccc          1          10               3      iii 
      4          ddd          3          10
      5          eee          3          20
      6          fff          2          10

expected output:

docname workstatus(10) workstatus(20)
ggg      1               0
hhh      2               1
iii      1               1

can also use temporary tables between the queries

Thanks in advance

Answer 1

Try this

Full working example

declare @patient as table(
patientID int IDENTITY(1,1) NOT NULL,
patientName varchar(25),
docID int,
workstatus smallint
)

declare @doc as table(
docID int IDENTITY(1,1) NOT NULL,
docname varchar(25)
)

insert into @patient
select 'aaa', 2, 10
union all
select 'bbb', 2, 20
union all
select 'ccc', 1, 10
union all
select 'ddd', 3, 10
union all
select 'eee', 3, 20
union all
select 'fff', 2, 10


insert into @doc
select 'ggg'
union all
select 'hhh'
union all
select 'iii'

select docname, 
      SUM(case when t1.workstatus = 10 THEN 1 ELSE 0 END) as [workstatus(10)],
      SUM(case when t1.workstatus = 20 THEN 1 ELSE 0 END) as [workstatus(20)] 
    from @patient t1
    inner join @doc t2 on t1.docid=t2.docid
    GROUP BY docname

Answer 2

Select  d.docname, 
        SUM(case when c.workstatus = 10 THEN 1 ELSE 0 END) as [WorkStatus(10)],
        SUM(case when c.workstatus = 20 THEN 1 ELSE 0 END) as [WorkStatus(20)] 
from created_patient_table c
inner join already_created_doc_table d on c.docid=d.docid
group by d.docid,d.docname