T-SQL | SQL-Server Pivot

Question

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

DESIRED RESULT

you have to show what have tried and why it failed. You cannot expect someone to post you entire solution and solve your problems — Kristijan Iliev, Sep 10 '15 at 06:40
Although including sample data and results is a good start, please see [ask] for tips on improving your question to increase your chance of getting an answer. — APH, Sep 10 '15 at 06:43
possible duplicate of [SQL Server PIVOT examples?](http://stackoverflow.com/questions/24470/sql-server-pivot-examples) — Felix Pamittan, Sep 10 '15 at 06:55
Add more sample data, it's not quite clear how to get the expected result. — jarlh, Sep 10 '15 at 06:57

score 0 · Accepted Answer · answered Sep 10 '15 at 06:46

0

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

answered Sep 10 '15 at 06:46

Praveen

8,945
4
31
49

1

I suppose SUM is what OP wants. (Same answer with sample data, I know, but if OP adds more sample data we will know.) – jarlh Sep 10 '15 at 06:56

T-SQL | SQL-Server Pivot

1 Answers1