calculating column totals using Pivot

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

calculating column totals using Pivot

Author

Topic

cognos79
Posting Yak Master

241 Posts

Posted - 2007-06-29 : 12:20:23

i do have query...this works fine. I want to add column totals...below is the desired output i am looking for.

QUERY:
Create table #Test(BatchID int ,Status int)

insert into #test select 1001 ,1
insert into #test select 1001 ,2
insert into #test select 1002 ,0
insert into #test select 1002 ,3
insert into #test select 1002 ,4
insert into #test select 1003 ,4
insert into #test select 1004 ,4

declare @Status varchar(50)
set @status = ''

Select @Status = @Status + '[' + convert(varchar(2),a.status )+ '],' from (Select distinct a.Status from #Test a) a

SET @Status = LEFT(@Status, LEN(@Status) - 1)

exec('SELECT * FROM #test p PIVOT( count(Status) for status in (' + @Status + ')) AS pvt ORDER BY BatchId')
drop table #test

OUTPUT:
0 1 2 3 4
1001 0 1 1 0 0
1002 1 0 0 1 0
1003 0 0 0 0 1
1004 0 0 0 0 1
Total: 1 1 1 1 1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-30 : 00:00:57

Where do you want to show data?
If you use front end application, then do summation there
If you use reports use SUM function there

Madhivanan

Failing to plan is Planning to fail

cognos79
Posting Yak Master

241 Posts

Posted - 2007-07-02 : 14:29:11

I want to know if there is a way to do this in SQL. Right now i am doing this in front end app and its taking 5 secs to do it. so wondering if i do this in sql, will it speed up the processing

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-07-03 : 00:09:59

Doing at the front end is good way...

back end code like this...
Select cast(BatchID as varchar(20)) as BatchId,
max(case when status = 0 then 1 else 0 end) as '0',
max(case when status = 1 then 1 else 0 end) as '1',
max(case when status = 2 then 1 else 0 end) as '2',
max(case when status = 3 then 1 else 0 end) as '3',
max(case when status = 4 then 1 else 0 end) as '4'
From #test
group by BatchID

union all

Select 'Total',
Sum(case when status = 0 then 1 else 0 end),
Sum(case when status = 1 then 1 else 0 end),
Sum(case when status = 2 then 1 else 0 end),
Sum(case when status = 3 then 1 else 0 end),
Sum(case when status = 4 then 1 else 0 end)
from #test

--------------------------------------------------
S.Ahamed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources