November 8, 2012 at 10:52 am
I would like to have a number of kpi's in my organisation per costcenter into a html-table to be mailed to the manager of that costcenter.
I know how to put the result set of the query in a html-table, but I would like to have the result set of the query as follows:
y-axis: the kpi's
x-axis: the costcenters
I can hard code the costcenters into the query but that's not a nice option in case new costcenters are made into our erp-system.
I would like to have a query that loops through the costcenter-table and puts all values as a separate column in the query automatically. This in order to avoid editing the query when a costcenter is added in case the costcenters are hard coded into the query. The kpi's will be the rows then.
What's the best way to do this using a SQL query?
I came accross the while-statement but I'm still not familiar with the correct syntax.
I hope to hear from you!
Thanks a lot!
November 8, 2012 at 11:11 am
michielbijnen (11/8/2012)
I would like to have a number of kpi's in my organisation per costcenter into a html-table to be mailed to the manager of that costcenter.I know how to put the result set of the query in a html-table, but I would like to have the result set of the query as follows:
y-axis: the kpi's
x-axis: the costcenters
I can hard code the costcenters into the query but that's not a nice option in case new costcenters are made into our erp-system.
I would like to have a query that loops through the costcenter-table and puts all values as a separate column in the query automatically. This in order to avoid editing the query when a costcenter is added in case the costcenters are hard coded into the query. The kpi's will be the rows then.
What's the best way to do this using a SQL query?
I came accross the while-statement but I'm still not familiar with the correct syntax.
I hope to hear from you!
Thanks a lot!
Looping in sql is generally very slow. There a few situation where looping is required but this does not sound like one of those. You should do this with a set based query. If you need some help with the code you should take a look at the first link in my signature for best practices when posting questions. This does in fact seem to one of those times. 😛
--EDIT--
I missed the part about email. :blush:
_______________________________________________________________
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/
November 8, 2012 at 11:12 am
michielbijnen (11/8/2012)
I would like to have a number of kpi's in my organisation per costcenter into a html-table to be mailed to the manager of that costcenter.I know how to put the result set of the query in a html-table, but I would like to have the result set of the query as follows:
y-axis: the kpi's
x-axis: the costcenters
I can hard code the costcenters into the query but that's not a nice option in case new costcenters are made into our erp-system.
I would like to have a query that loops through the costcenter-table and puts all values as a separate column in the query automatically. This in order to avoid editing the query when a costcenter is added in case the costcenters are hard coded into the query. The kpi's will be the rows then.
What's the best way to do this using a SQL query?
I came accross the while-statement but I'm still not familiar with the correct syntax.
I hope to hear from you!
Thanks a lot!
Lookup sp_send_dbmail in Books Online (the HELP system that comes with SQL Server). That page has an example of how to build an HTML table into the body of an email near the end of the page.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply