February 10, 2012 at 12:08 pm
I have a table with the following data:
HostNameServerGroup
Exchange1Messaging
Exchange2Messaging
DC1DomainControllers
DC2DomainControllers
Web1WebServers
Web2WebServers
How do I write a dynamic query that returns the ServerGroup and the number of servers? I know I can do a select count from table1 where servergroup like 'Messaging', but I'd like to be able to dynamically get the servergroup names dynamically and the associate counts.
Thanks!
Dale
February 10, 2012 at 12:42 pm
It is best for these types of questions if you can post ddl and sample data along with desired results based on your sample data. I created the ddl and sample data for you. I think this is what you are after.
Create table #ServerList
(
HostName varchar(25),
ServerGroup varchar(25)
)
insert #ServerList(HostName, ServerGroup)
values
('Exchange1', 'Messaging'),
('Exchange2', 'Messaging'),
('DC1', 'DomainControllers'),
('DC2', 'DomainControllers'),
('Web1', 'WebServers'),
('Web2', 'WebServers')
select ServerGroup, COUNT(*) as Hostcount
from #ServerList
group by ServerGroup
drop table #ServerList
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 10, 2012 at 12:46 pm
Thanks! Sorry for not providing all the data.
Dale
February 10, 2012 at 12:47 pm
No worries. Now you have an example of what you should provide for next time. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply