SQL Crosstab query without using transform - subquery

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,
 
(Select 
   trans.emp
   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 )
 
from
   employees 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

Open in new window

jransom2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HwkrangerCommented:
A while ago, I found something similiar to this:

http://weblogs.sqlteam.com/jeffs/articles/5120.aspx

I use the original crosstab they wrote, but this works just as well.
0
ErnariashCommented:
Hope this will help. Thanks...

select emp.name, emp.department, dept.deptname, emp.manager, mgr.name, other.field,
(Select   sum(trans.tranamt)
   from transactions trans
   where trans.employee = emp.employee and  trans.acct = 'Travel')  as 'Travel' ,
 (Select   sum(trans.tranamt)
   from transactions trans
   where trans.employee = emp.employee and  trans.acct = 'Meals')  as 'Meals'   ,
 (Select   sum(trans.tranamt)
   from transactions trans
   where trans.employee = emp.employee and  trans.acct = 'Airfare')  as 'Airfare'  
 
from
   employees 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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jransom2Author Commented:
Beautiful... I knew it was going to be something simple.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.