SQLTeam.com | Weblogs | Forums

Need detail lines combined on report


#1

I need to show multiple detail records on the same report line. As an example my records look like this:

Payroll1,State tax, 100
Payroll1,Fed tax, 300
Payroll2, State tax, 110
Payroll2, Fed tax, 302

I need the report to look like this:
Payroll State Tax Fed Tax Total Tax

Payroll1 100 300 400
Payroll2 110 302 412

Any help will be greatly appreciated


#2

If your using a matix then just put the Tax field as a Group Column


#3

or to make the change in sql just do a pivot

drop table test1

create table test1(

Payroll varchar(30),
Tax varchar(30),
[Total Tax] int)

insert into test1
select 'Payroll1' as [Payroll],'State tax'as Tax, 100 as [Total Tax]
union all
select 'Payroll1' as [Payroll] ,'Fed tax'as Tax, 300 as [Total Tax]
union all
select 'Payroll2' as [Payroll],'State tax'as Tax, 110 as [Total Tax]
union all
select 'Payroll2' as [Payroll],'Fed tax'as Tax, 302 as [Total Tax]

--select * from test1

select
Payroll
,[State tax]
,[Fed tax]
from(
select * from test1 ) p pivot (

sum([Total Tax]) for [Tax] in
(
[State tax]
,[Fed tax]))
m