I am trying to perform a subquery or transform of sorts using SQL Server. I think I have done something similar in Oracle, but cannot locate it.
The actual query I am working on pulls about 50 fields from 7 tables and I would like to avoid a huge "group by" statement. All table except the transaction tables have single records per employee, but the transaction table can have many records.
select emp.name, emp.department, dept.deptname, emp.manager, mgr.name, other.field,
sum(case when trans.acct = 'Travel' then trans.tranamt else 0 end) as 'Travel',
sum(case when trans.acct = 'Meals' then trans.tranamt else 0 end) as 'Meals',
sum(case when trans.acct = 'Airfare' then trans.tranamt else 0 end) as 'Airfare'
from transactions trans
where trans.employee = emp.employee
group by trans.emp )
left join departments dept on emp.department = dept.department
left join employees mgr on emp.manager = mgr.employee
left join AnotherTable other on emp.employee = other.employee