cross tabs quesry?
-
umh..got disturbed by the little mini-me. Needs to be fed. Now I have some two hours time left.
To finish my post: most front-end languages have very sophisticated functionalities for this.
It's good to know that you can do it in SQL, if you need to, but it really most not be done.
Ever tried converting decimals into hexadecimals in T-SQL?
I have a script for this at the office, a real sh*tty way with T-SQL, but an easy warming up exercise for every programming language.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Looking beyond this example and considering a typical crosstab query...
Are you saying that the grouping and aggregation done in a typical crosstab query is more of a presentation task to be done at the client than a data retrieval task to be done at the server?
I'm very curious about what brings you to that conclusion.
Do you have an example to support "most front-end languages have very sophisticated functionalities for this"?
-
quote:
She decided I wasn't enough of an expert and reposted her question to another SQL Server list!
"Against stupidity the gods themselves struggle in vain."
An indeed true sentence from one of the greatest germans ever around.
...back in those days when this country was something special.
quote:
I have the book I referenced at home, so I checked it last night. Rozenshtein and his co-authors trademarked (not copyrighted) the name. I suppose the idea interests you because you like history. Here's an article on characteristic functions in ancient T-SQL:
You are right! I really like history and I think it won't hurt my to build up a solid background on the theory behind. And what hold also true is the fact that one should NEVER rely on only one source of information.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
Looking beyond this example and considering a typical crosstab query...Are you saying that the grouping and aggregation done in a typical crosstab query is more of a presentation task to be done at the client than a data retrieval task to be done at the server?
I'm very curious about what brings you to that conclusion.
Me and Joe Celko share the same opinion on this
Yep, and it drives me nuts to read that; a cross tab is a report, not a
query; a pivot table is not a table (what entity or relationship does it
model?) and the term "pivot" is something that a guy who never took a
statistics class made up at microsoft becuase he did not know the term
cross tab.
...quote:
Do you have an example to support "most front-end languages have very sophisticated functionalities for this"?
- Access has a wizard for this
- Excel is very strong in transposing
- Not sure about it, but VB's GetRows() can also be used for this, IIRC
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
Looking beyond this example and considering a typical crosstab query...Are you saying that the grouping and aggregation done in a typical crosstab query is more of a presentation task to be done at the client than a data retrieval task to be done at the server?
I'm very curious about what brings you to that conclusion.
Do you have an example to support "most front-end languages have very sophisticated functionalities for this"?
Answering for myself, not Frank...
Rather than coding loops in T-SQL, which is not something it's good at, every front-end language I've ever used is very good at loops (yes, even Lisp, if you recurse). Use SQL to return the data in a relational form, e.g. what the OP has:
prodId prodName attribName attribValue
0 vwBeetle height 100
0 vwBeetle length 200And then loop through the recordset at the client to pivot the values. If the number of attributes is varies, using T-SQL will tempt you to use dynamic SQL (as seen in the code some have posted), which has several downsides including poor performance and major security issues (http://www.algonet.se/~sommar/dynamic_sql.html).
My hard-earned opinion is that reformatting data from its natural relational structure is indeed a presentation issue, and should be handled at a higher level than SQL. I hate making a dedicated server perform tricks like this for which it is not designed nor suited. Making SQL Server do this is like Dr. Johnson's dancing dog: "It is not done well; but you are surprized to find it done at all."
I cringe to think of all the dancing that will be forced upon Yukon with the CLR extensions.
--Jonathan
--Jonathan -
quote:
I cringe to think of all the dancing that will be forced upon Yukon with the CLR extensions.
didn't care too much about Yukon yet.
You mean, it will be possible to programm SQL Server with VB.Net?
If so, good night, set based theory, back to the caves of row by row processing.
I believe there is a good reason that extended sprocs could only be written in other programming languages as VB.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
"Against stupidity the gods themselves struggle in vain."
An indeed true sentence from one of the greatest germans ever around.
...back in those days when this country was something special.
I like Schiller, but I think Dilbert (Scott Adams) put it even better: "Argue with an idiot and he will drag you down to his level and beat you with experience."
quote:
You are right! I really like history and I think it won't hurt my to build up a solid background on the theory behind. And what hold also true is the fact that one should NEVER rely on only one source of information.
I wouldn't call this theory, but instead a clever hack.
--Jonathan
--Jonathan -
quote:
I like Schiller, but I think Dilbert (Scott Adams) put it even better: "Argue with an idiot and he will drag you down to his level and beat you with experience."
While Schiller is certainly a class of its own, Dilbert is more intuitive to the majority of people.
quote:
I wouldn't call this theory, but instead a clever hack.
Sorry, didn't find the right word for it.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
This is the first time I hang around on the forum in the evening.
Hey, I'm really looking forward to my notebook I get next week and the company sponsored DSL account ?!?!(need a little more work right now)
It's much more active now than during european working hours
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
The arrogance that one finds in all the SQL Server forums is ever amazing, and Joe Celko is the worst.
Neither Access nor Excel are front-end languages.
I tend to think that T-SQL is quite good at grouping and aggregation, which are key aspects of a typical crosstab query.
I totally agree with the concern about misuse of the CLR in Yukon, but I fail to understand how insisting that a procedural language do grouping and aggregation is a rational reaction.
It appears that the views of some DBAs are rather myopic. Some DBAs consider applications to be languages that can be used to develop other applications. Some DBAs seem unwilling to tolerate having SQL Server do anything that could possibly be done at another layer, so as to keep their database as pristine as possible. Alas, the world extends beyond DBAs. They, their languages, and their databases have to share the universe with other entities and other considerations.
-
quote:
While Schiller is certainly a class of its own, Dilbert is more intuitive to the majority of people.quote:
I wouldn't call this theory, but instead a clever hack.
Sorry, didn't find the right word for it.
Es tut mir leid. I should have wriiten "funnier," not "better."
As the DBRanger seems to be addressing me... SQL is certainly doing the "grouping and aggregation," it's the presentation of these groups that it isn't good at. You are certainly free to use whatever means you like, but I consider the term "arrogant" to be pejorative. Admitting the failings of one's platform seems to me the opposite of arrogance. I fail to understand how one can use SQL Server without some sort of front end and if the front end is better suited for something than is SQL Server (and frees the server to do what it is good at), then it makes sense to code it there. A DBA is usually tasked with using a scarce resource most efficiently; this is not arrogance, it's economics. If one is supporting a few users with an undertasked server, and doesn't need the sophisitication of a tiered model, then I suppose anything that gets the job done is fine. But you're also adding the hidden cost of supporting, maintaining, and securing convoluted and assailable code.
--Jonathan
--Jonathan -
Some quotes from this thread...
"I understand that Access even has a built-in function for this, so I guess one could also just link into the SQL tables from Access and then do it there."
"- Access has a wizard for this"
"- Excel is very strong in transposing"
"Use SQL to return the data in a relational form"
"loop through the recordset at the client to pivot the values"
"reformatting data from its natural relational structure is indeed a presentation issue, and should be handled at a higher level than SQL"
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? 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.
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? Transposing data with Excel is not a crosstab, 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. 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.
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
-
quote:
can't let loose from work?
Nah! Week hols Frank, just keeping in touch with the forum
quote:
Do this at the client. The server should be used for data retrieval, not presentation.
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
quote:
I suggested she just write a little Excel VBA to write the values across
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.
quote:
Hey, I'm really looking forward to my notebook I get next week and the company sponsored DSL account
Frank, lucky or what
Far away is close at hand in the images of elsewhere.
Anon. -
David
quote:
For problems like this cursors seem the best bet as trying to build a dynamic query can sometimes exceed variable size limits.
not really - as long as you have some idea of the likely size, you can extend the length more or less (less) indefinitely. I've tried exec() with 100 parameters (confusingly the members of the array of parameters to the exec function are seperated by what look like concatenation operators) each of 8000 chrs and it works fine. SQL generated with Excel functions, not by hand, I should add...
This snippet from the code I sent gives the idea:
quote:
--if the next string would overflow the variable,--shift the variables along to make more room.
--otherwise, just add the new string on
if @fixed_len + len(@sql) + len(@sql6) > 8000
select @sql4 = @sql5, @sql5 = @sql6, @sql6 = @sql
else
set @sql6 = @sql6 + @sql
as you might say, not elegant but functional (and not inelegant either). I reckon the SQL is likely to give better perfomance than a cursor - even given the need to compile and lack of execution plan.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
quote:
When somebody considers himself/herself to be "enough of an expert", that suggests arrogance.
I agree
Correction: After rereading the whole thread, I disagree with that! And I suggest you should also read the context in which this statement was made originally.
quote:
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.
Might be or might be not. But even more foolish it is not to take some else's advice. If I'm not willing to hear other opinion's, I don't ask questions.
quote:
When somebody proudly aligns himself/herself with an extremely arrogant person (such as Joe Celko), that suggests arrogance.
Might be or might be not.
Celko is what he is, no one will change that. When I follow M$ newsgroups, I only do this, because of the entertaining aspects of Celko's post
Actually I have no intention to convince someone of my opinion!
Note, the only reason why I quoted Celko, was, that he gave the first answer I saw, where cross tabs are put in the right context.
The keyword in Celko's posting is statistics.
Ever happened to attend statistics classes yourself?
While I don't have english beginners textbooks on statistics, you might want to google on cross tab (aka contingency tables).
Now I'm trying to explain what this is. Bear with me, if I can't find some right words
A contingency table is a table showing the responses of subjects to one variable as a function of another variable.
Contingency tables are used in statistics to analyze multidimensional data.
Note that I mentioned Analysis. This is more than just retrieving the raw data.
And that's why I think it should be done at the client.
Frank
Edited by - Frank Kalis on 11/03/2003 04:53:23 AM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply