JKumar JKumar - 7 months ago 30
SQL Question

T-SQL | SQL-Server Pivot

This is the current query:

SELECT
cod.COM_DESC
,count(emp.OBE_Name) colValue
,'Total'[Desc]
FROM OBP_EMP_MASTER emp
LEFT JOIN COMMONCODES cod
ON emp.OBE_AGENT_DR = cod.COM_SLNO
WHERE OBE_AGENT_DR IS NOT NULL
GROUP BY cod.COM_DESC

UNION ALL

SELECT
cod1.COM_DESC
,count(rep.OBE_Name) colValue
,'Replaced'[Desc]
FROM OBP_EMP_REPLACE_HISTORY rep
LEFT JOIN COMMONCODES cod1
ON rep.OBE_AGENT_DR = cod1.COM_SLNO
WHERE OBE_AGENT_DR IS NOT NULL
GROUP BY cod1.COM_DESC


RESULT

enter image description here

DESIRED RESULT

enter image description here

Answer

Try:

select
    Agent,
    max(case when `desc` = 'Total' then ColValue else 0 end) Total,
    max(case when `desc` = 'Replaced' then ColValue else 0 end) Replaced
from tbl
group by Agent  

Demo sqlfiddle