CODEWITHSUNDEEP
sql-serverjoinpivot-table
psyborg.eth
psyborg.eth

Reputation: 320

How to convert column to row in sql without using pivot

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 a foreign key referencing 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 till now is :

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 ?

Upvotes: 0

Views: 194

Answers (1)

GMB
GMB

Reputation: 222622

I would recommend conditional aggregation:

select s.staffid, s.staffname, 
    sum(case when l.leadstatus = 'New'       then 1 else 0 end) as newLeadCount,
    sum(case when l.leadstatus = 'Qualified' then 1 else 0 end) as qualifiedLeadCount
from TableStaff s
inner join TableLead l on l.staffid = s.staffid
group by s.staffid, s.staffname

Upvotes: 2

Related Questions