1 Result Set with n row instead of n RS with 1 row

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 2000 Forums

Transact-SQL (2000)

1 Result Set with n row instead of n RS with 1 row

Author

Topic

calgarychinese
Starting Member

5 Posts

Posted - 2006-05-03 : 20:22:03

Hi there

I know the title sounds stupid but I don't really know the best way to word it.

I have to do a count of 2 employees in several tables. So I can do for each table that I want to get a count, such as

SELECT 'tblA' AS tblName, 
(SELECT Count(*) FROM tblA WHERE EmployeeId = 'EMP1') AS CounterEMP1, 
(SELECT Count(*) FROM tblA WHERE EmployeeId = 'EMP2') AS CounterEMP2 

SELECT 'tblB' AS tblName, 
(SELECT Count(*) FROM tblA WHERE EmployeeId = 'EMP1') AS CounterEMP1, 
(SELECT Count(*) FROM tblA WHERE EmployeeId = 'EMP2') AS CounterEMP2

But say if I have 15 tables to look at, I'll end up having 15 result sets.

Is there a way to rewrite it so, instead of having 15 result sets, each with 1 row, I can have 1 result set of 15 rows?

I suppose you can do a cursor and/or stored procedure, but I'd like to know if there's a way to do just one SELECT statement to accomplish the above.

Thanks
CalgaryChinese

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-03 : 20:45:59

This will be faster than the subquery count as it only need to read the table once. And you can also put the result into a temp table for each table count and select from the temp table at the end.


create table #temp
(
    tblName     varchar(50),
    CounterEMP1 int,
    CounterEMP2 int
)

insert into #temp
select	'tblA' as tblName,
        count(case when EmployeeId = 'EMP1' then 1 end) AS CounterEMP1,
	count(case when EmployeeId = 'EMP2' then 1 end) AS CounterEMP2
from	tblA 

insert into #temp
select	'tblB' as tblName,
        count(case when EmployeeId = 'EMP1' then 1 end) AS CounterEMP1,
	count(case when EmployeeId = 'EMP2' then 1 end) AS CounterEMP2
from	tblB

select * from #temp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-04 : 03:09:49

Also read about cross-tab reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources