cross tabs quesry?
-
quote:
Agree, mostly, but not always desirable. I do my cross tabs on the client. Have done some in tsql but mostly for analysis and if memory serves me right not in production. A lot depends on the software doing the presentation, Excel with VBA is fast but Excel via VB is slow
I guess, Excel is used in most companies. It is 'relative' inexpensive software compared to Prophet, which IMHO is a pure statistical software package and which I use frequently.
quote:
I use Excel to extract data from sql for user reporting. Users love it as they get the results and can then analyse the data.Don't forget the average user is far more familiar with Excel than databases. There is little or none fear to get in touch with Excel.
quote:
Frank, lucky or whatTime will tell. Anyway, a smart move by the company to get me to work more without additional payment.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
When somebody proudly aligns himself/herself with an extremely arrogant person (such as Joe Celko), that suggests arrogance.Last comment from me to this topic!
Did you notice the smily? Do you know the film 'Blues Brothers'? There are at least in the german translation, several sequences where John Belushi states:
'Me and the holy god have an agreement..' or something similar.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
After saying or supporting all of this, how can you now say that SQL Server would be doing the grouping and aggregation? How exactly do you define a typical crosstab query and what steps does it involve?Here is an analogue of the cross-tab query that the OP is asking for:
USE pubs
go
CREATE PROC dbo.Royalties @Type char(12) AS
SET NOCOUNT ON
SELECT t.Title, a.au_LName, ta.RoyaltyPer
FROM dbo.Titles t JOIN dbo.TitleAuthor ta ON t.Title_ID = ta.Title_ID
JOIN dbo.Authors a ON ta.au_ID = a.au_ID
WHERE t.Type = @Type
ORDER BY t.Title_IDThe ORDER BY clause is doing the grouping, and the denormalized set returned provides all the data necessary for the report.
quote:
What exactly do you mean by "presentation" in this context? Nobody has suggested that SQL Server should render a polished crosstab report to a file ready for spooling to a printer. However, it's clearly being suggested above that SQL Server should send any necessary amount of raw relational data to a client and let the client do the grouping and aggregation. Both of these extremes seem foolish.That's not "raw relational data," but instead a (denormalized) result set containing the necessary data, accomplished by one cheap and easy statement needing only execute permission on the SP itself.
quote:
Since you need the "sophisitication of a tiered model" let's consider that environment. Let's assume that the client is the usual web browser. The web browser is not going to do the crosstab and you do not want the crosstab to be done with SQL Server. That leaves the middle tier, which involves IIS. What do you advise for doing the crosstab in the middle tier?A report writer, e.g.:
http://www.crystaldecisions.com/products/crystalreports/appdev/keyfeatures.asp
quote:
Transposing data with Excel is not a crosstab...Ever heard of Excel's Pivot Table functionality? We use this to publish interactive pivot tables on intranets using OWC.
quote:
...so that leaves Access. Access can take the raw relational data from SQL Server and use it's own SQL engine to do the crosstab.I don't know much about Access, but I know that people use it for report writing and I had read that it has cross-tab functions. That's why I supposed one might use it for this. Obviously not in your specific example, though.
quote:
Would you have IIS instantiate Access to do the crosstab and feed the result back to IIS for conversion to HTML? That would fit good with your task of "using a scarce resource [the database server] most efficiently". The database server would get off pretty easy, although sending a large amount of raw relational data to the web server may take some time. It would not matter to you what happens at the web server, you're the DBA. It's not your problem.As my job includes consulting with clients to optimize their systems, I tend to keep scaling considerations in mind. It is far easier to scale out at an intermediate tier than it is to scale SQL Server up or out (another weakness of SQL Server). The above example SP may or may not send more data than will a cross-tab presentation: it depends upon several factors including the length of the grouping columns and the number of null intersections. Here is the cheapest analogous cross-tab formatted SQL I can think of:
CREATE PROC dbo.RoyaltiesXT @Type char(12) AS
SET NOCOUNT ON
DECLARE @sql nvarchar(4000)
SELECT @sql = ISNULL(@sql, 'SELECT t.Title') + ',
MAX(CASE a.au_LName WHEN ' + QUOTENAME(a.au_LName,'''')
+ ' THEN ta.RoyaltyPer END) [' + a.au_LName + ']'
FROM dbo.TitleAuthor ta JOIN dbo.Authors a ON ta.au_id = a.au_id
JOIN dbo.Titles t ON ta.Title_Id = t.Title_Id
WHERE t.Type = @Type
GROUP BY a.au_LName
SET @sql = @sql + '
FROM dbo.Titles t JOIN dbo.TitleAuthor ta ON t.Title_Id = ta.Title_Id
JOIN dbo.Authors a ON ta.au_ID = a.au_ID
WHERE t.Type = @Type
GROUP BY t.Title'
EXEC sp_ExecuteSQL @sql, N'@Type char(12)', @Type = @TypeThis takes SQL Server twice as long to execute as does the first query. It also requires granting SELECT permission on three tables. If the data is dynamic, one might also need to atomize the SP, which could further hinder performance of the server in a multi-user environment. Supporting and maintaining this non-portable code is beyond the capabilities of many programmers, which is why I characterized it as "convoluted" compared to the first SP. Changing the restriction clause for the report would require someone with special knowledge of SQL Server rather than a developer who could rewrite a simple select statement and use a report writer.
quote:
When somebody considers himself/herself to be "enough of an expert", that suggests arrogance.When somebody proudly quotes "Against stupidity the gods themselves struggle in vain." while clearly attributing the stupidity to others (leaving himself/herself to be the god), that suggests arrogance.
When somebody proudly aligns himself/herself with an extremely arrogant person (such as Joe Celko), that suggests arrogance.
When sombody holds himself/herself up as the "opposite of arrogance", that suggests arrogance.
When sombody dismisses another DBA who has a differing opinion as "supporting a few users with an undertasked server", that suggests arrogance.
When somebody implies that another DBA who has a differing opinion writes "convoluted and assailable code", that suggests arrogance.
If the shoe fits...
Edited by - DBRanger on 10/31/2003 7:21:38 PM
I arrogantly choose to ignore ad hominem attacks, but I did want to quote this in my reply in case you edit your post again.
--Jonathan
--Jonathan
Viewing 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply