Convert columns to rows without using pivot

Question

I have been assigned to get the data in required format from two tables.

TableStaff :

STaffID | Staff Name
--------------------
1      | John 
2      | Jack

and TableLead

LeadID | LeadValue | LeadStaus | StaffID
----------------------------------------
  1    | 5000      | New       | 1
  2    | 8000      | Qualified | 1
  3    | 3000      | New       | 2

As you will notice StaffID is the foreign key to TableStaff.

I have to represent the data in following format

StaffID | StaffName | NewLeadCount | QualifiedLeadCount 
-------------------------------------------------------
1       | John      |  1           |   1
2       | Jack      |  1           |   0

What I have tried:

SELECT 
    count([LeadID ]) as LdCount , 
    'New' as StageName  
FROM 
    [dbo].[TableLead] 
where 
    [LeadStaus] = 'New' 
UNION 
SELECT 
    count([LeadID ]) as LdCount , 
    'Qualified' as StageName 
FROM 
    [dbo].[TableLead] 
where 
    [LeadStaus] = 'Qualified ' 

Any NULL spots should be replaced by 0. Can anyone show me the right direction to approach the problem? Without using pivot (because of its restrictive nature).

Answer 1

You can use a SUM(CASE... and a GROUP BY

SELECT
    TableLead.StaffID, 
    Staff.StaffName,
    SUM(CASE WHEN TableLead.LeadStatus = 'New' THEN 1 ELSE 0 END) NewLeadAccount,
    SUM(CASE WHEN TableLead.LeadStatus = 'Qualified' THEN 1 ELSE 0 END) QualifiedLeadAccount
FROM
    TableLead
JOIN
    Staff
    ON Staff.StaffID = TableLead.StaffID
GROUP BY
    TableLead.StaffID,
    Staff.StaffName;

StaffID | StaffName | NewLeadAccount | QualifiedLeadAccount
------: | :-------- | -------------: | -------------------:
      1 | John      |              1 |                    1
      2 | Jack      |              1 |                    0

db<>fiddle here