Returning counts as a list - SQL Server Forums

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)

Returning counts as a list

Author

Topic

dmilam
Posting Yak Master

185 Posts

Posted - 2010-08-17 : 14:29:34

I'm thinking this is fairly straightforward, but I cannot get to the solution.


declare @hold table (
tmp varchar(50) not null
)

insert @hold

select 
	'T..'+name 
from 
	sys.tables
where 
	name like '%_tmp'

select * from @hold

I get what I need, a list (column) of tables.

Now, I want to get a list (column) of the count the number of customers in each table, but a derived table isn't the way, at least not here (syntax error, for one):


select count(distinct customerid)
from (select * from @hold)

How to return such a list?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-17 : 15:23:04

you need dynamic sql for that to loop through and execute count(*) from dynamic sql.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dmilam
Posting Yak Master

185 Posts

Posted - 2010-08-17 : 15:27:11

quote:
Originally posted by visakh16

you need dynamic sql for that to loop through and execute count(*) from dynamic sql.

Thanks; I'll see what I can devise.

dmilam
Posting Yak Master

185 Posts

Posted - 2010-08-17 : 16:14:05

Feeling pretty dumb today. Stuck on using a loop with dynamic sql. Can't think.

dmilam
Posting Yak Master

185 Posts

Posted - 2010-08-17 : 16:39:17

Got this to work so far:


declare @sql as nvarchar(4000)

set @sql = 
'
select count(distinct(customerid))
from ' + (select top 1 * from @tables)

exec sp_executesql @sql

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources