SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Crosstab query in SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

fragglefish
Starting Member

United Kingdom
2 Posts

Posted - 10/15/2005 :  06:58:27  Show Profile  Reply with Quote
Hello

I am trying to create a crosstab query in SQL. It is all done within one table (dbo.contacts). I want to use the appointmentagency and appointmentdate field to create a crosstab query detailing how many appointments i have booked for each appointmentageny for the next 7 days.

appointmentdate ---->
appointmentagency
|
|
\/

the appointmentageny field is just a text field. The appointmentdate field is a date time.. but i have written a function the extracts the date only... (dbo.fn_dateonly).

I am really struggling with this i havent as yet been able to write a crosstab query in SQl and would appreciate any and all assistance that you could offer. If anyone has any ideas you could email me on. dave.clarke@quaycomms.co.uk

Cheers

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 10/15/2005 :  07:07:01  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
check out for this link..

http://www.sqlteam.com/item.asp?ItemID=2955



Complicated things can be done by simple thinking
Go to Top of Page

fragglefish
Starting Member

United Kingdom
2 Posts

Posted - 10/15/2005 :  07:41:39  Show Profile  Reply with Quote
Hello

I have followed the link and am still having some problems with the code. i used

EXECUTE spcrosstab 'SELECT [appointmentagency] FROM dbo.contacts c
WHERE appointmentdate > getdate()+7 and appointmentdate < getdate()+14,
GROUP BY appointmentagency', 'count(appointmentagency)', 'appointmentdate', 'contacts'

And i get the following error message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'app'.

Does the code look correct?
Go to Top of Page

karuna
Aged Yak Warrior

581 Posts

Posted - 10/15/2005 :  11:27:55  Show Profile  Send karuna a Yahoo! Message  Reply with Quote
I think you are missing/having extra a single qoute in your stored procedure...
Print the select statement and check it.

Karunakaran
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 10/15/2005 :  16:34:52  Show Profile  Reply with Quote
Crosstab queries with dynamic column headers are notoriously difficult to do in TSQL. But wise DBAs know that there is simply NEVER a good reason to do dynamic crosstabs in SQL Server.
Why?
Because the dynamic nature of the resulting dataset makes it impossible to use, not only in any further server-side processing, but for any canned reporting application (such as Crystal) as well. And if you are generating your report using Query Analyzer, well, guess what? QA is a development tool, not a reporting tool!
Export your data to a true reporting tool, such as Active Reports, Crystal Reports, MS Access, or Microsoft Excel, all of which can create sexy crosstabs at the push of a button.
SQL Server is a server only, and should not be used for either reporting or data entry.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 10/15/2005 :  19:34:52  Show Profile  Visit robvolk's Homepage  Reply with Quote
quote:
Originally posted by blindman

Crosstab queries with dynamic column headers are notoriously difficult to do in TSQL.
Good thing Jeff doesn't agree with ya:

http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx

Neither do I, for that matter.
quote:
But wise DBAs know that there is simply NEVER a good reason to do dynamic crosstabs in SQL Server.
Why?
Because the dynamic nature of the resulting dataset makes it impossible to use, not only in any further server-side processing, but for any canned reporting application (such as Crystal) as well.
Crystal can certainly do cross tabs, but if you don't have it already it is simply not worth spending money on just to get them.

And I do plenty of extra server-side processing of cross tabs using the code in the article (I'm biased, I wrote it) It's also easy to extend to do more advanced processing than would be possible or convenient in Crystal. In fact, the only thing that makes them "impossible" to use is trying to use it with Crystal; ASP/ASP.Net handles them very nicely in fact.
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 10/16/2005 :  01:54:51  Show Profile  Reply with Quote
You are entitled to your opinion, but mine is not based upon a lack of experience either.
I use crosstabs pretty frequently, but only with fixed column headers. After 10 years of database application development and hundreds of reports, I have NEVER needed to use SQL Server to create dynamic crosstabs for the presentation layer. It makes an interesting coding problem, but for practical use I consider maSQLbation.
As far as Crystal goes, heck, I wasn't recommending it. I think Crystal is one of the worst application on the market. But no cross-tabing you do in SQL Server is going to be as powerful or as easy as using a pivot table in Microsoft Excel, and practically everybody already has a copy of that.
I stand by the principle that SQL Server is not a presentation layer, query analyzer is not a reporting tool, and neither should be used as such.
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 10/16/2005 :  01:56:39  Show Profile  Reply with Quote
maSQLbation: code that is fun to write, but not necessarily productive.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 10/16/2005 :  08:04:00  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Actually, I DO agree with blindman!

http://weblogs.sqlteam.com/jeffs/archive/2005/05/11/5101.aspx

I am not a fan of dynamic column headers in sql at all. Though I do show a way to do it in 1 blog post, I spend two others showing easy ways to do it in .NET and ASP, and also another to demonstrate that NOT crosstabbing in T-SQL but rather doing it at the presentation layer is (in at least 1 example, anyway) actually much more efficient.

See: http://weblogs.sqlteam.com/jeffs/archive/2005/05/12/5127.aspx
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 10/16/2005 :  10:21:34  Show Profile  Reply with Quote
Yeah, I saw your statement at the end of one article: "To make a long story short: Pivoting at the presentation layer results in clearer, shorter code and much better performance."
There are other reasons for leaving data presentation out of the database layer as well.
Which doesn't mean that your dynamic cross-tab code isn't impressive. I thought it was very elegant, and wish I had written it. I must sheepishly admit that I, too, engage in maSQLbation occasionally.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000