473,703 Members | 2,738 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Crosstab Qry with dynamic field names

Hi,

I am trying to create a stored procedures (SQL 7.0), to provide data
in
a crosstab format.
(I'm using Crystal Reports 8.5, but the Crosstab capabilities are
terrible, so I have to do as much as possible on the SQL side)

I have a table [Occurrences] with the following fields:
Year (int)
Month (int)
Occurs (int)
Claims (int)
I need a query to give me the following format:
Acct_Month 2001 2002 2003
Occurs Claims Occurs Claims Occurs Claims

January 120 180 132 196 110 140
February 154 210 165 202 144 178
March etc..
.....

Catch! I need the Year field name to be the contents of the field
Year in the Table (2001, 2002, 2003...). Not the usual Year_1, Year_2
approach.

I got the month name ok...
Acct_Month = DATENAME(month, Convert(Varchar (2), Month) + '/01/'+
Convert(Char(4) ,Year))

Is it possible to do this easely, without the use of cursors?

Any help would be much appreciated.

Luis Pinto
Jul 20 '05 #1
1 5226
Luis (lu*******@att. net) writes:
I need a query to give me the following format:
Acct_Month 2001 2002 2003
Occurs Claims Occurs Claims Occurs Claims

January 120 180 132 196 110 140
February 154 210 165 202 144 178
March etc..
....

Catch! I need the Year field name to be the contents of the field
Year in the Table (2001, 2002, 2003...). Not the usual Year_1, Year_2
approach.

I got the month name ok...
Acct_Month = DATENAME(month, Convert(Varchar (2), Month) + '/01/'+
Convert(Char(4) ,Year))

Is it possible to do this easely, without the use of cursors?


It takes a whole of dynamic SQL to get there.

You should probably investigate what RAC can do for you, see
http://www.rac4sql.net/.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
17666
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to Create a Dynamic Crosstab Report PRODUCT :Microsoft Access PROD/VER:1.00 1.10 OPER/SYS:WINDOWS
1
2299
by: Steve | last post by:
I have looked through the newsgroup for an answer to this but haven't been able to find anything resembling my situation. What I want to do is relatively simple, I think. I have a crosstab query that is bound to a report I am creating. I would like the column headings to be the name of the correspoding field name. For example, I have field names ALS, BCD, HLP, and the detail section prints the values for these fields. I would like...
1
3336
by: Richard Hollenbeck | last post by:
Hello Newsgroup. You have all been very helpful in the past and I thank you. I try to ask relevant questions so that they don't just benefit me, but also benefit the group. I'm currently overwhelmed by useless examples across the web on how to make "dynamic crosstab reports" without myself having a basic understanding about how to retrieve and assign recordsources, etc., from fields in a query to fields in the report. I see all these...
3
3594
by: deejayquai | last post by:
Hi I've created a crosstab query and displayed it as a sub-report in my main report. This is fine until the data changes and the column names become incorrect. I know I have to create a 'dynamic crosstab query' but I don't know how to!! I've read the "How to..." on the Microsoft site but it mainly gives an example rather than explain the basics, which I can't work out. My context is:
2
2937
by: deejayquai | last post by:
Hi I'm trying to produce a report based on a dynamic crosstab. Ultimately i'd like the report to actually become a sub report within a student end of year record of achievement. The dynamic sub-report will capture what grades the student has achieved in a list of different subjects and the reason I need it to be dynamic is that students take different subjects. Basically I've been trying to doctor the KB article on dynamic
2
5068
by: Joe-Paul | last post by:
Hi: I'm using a "CrossTab" Query to retrieve data. When I get it back, I want to print the column Headers along the first row of the grid. How to I get the names of the column Headers? The names of the columns will be dynamic; i.e., I am listing facilities and getting total transports provided for each facility. I am returning the Name of the Facility (or it's designator) as Column headers and want that in the first row...with the...
13
17158
by: salad | last post by:
Operating in A97. I didn't receive much of a response conserning Pivot tables in Access. Pivot tables are nice, but a CrossTab will work for me too. Using a Pivot table, one is actually launching Excel for data viewing. I'd prefer the user stay in Access. Creating dynamic crosstab queries is pretty simple. The problem is that the column count may shrink or grow depending on the filter.
2
2918
by: Keith Wilby | last post by:
I have a report that is based upon a crosstab query which return only the columns (fields) it has data for. When my report runs it sometimes fails because some of the text boxes don't have a field in the query. I've written some code to "fake" the missing field names which works perfectly but is quite complex and I'm wondering if there's a simple light bulb shining somewhere that I'm missing. The field names returned by the crosstab are...
0
2320
by: Peter Herath | last post by:
I want to create a custormizable report . For an example, there's a form with four combo boxes and two of them having database tables columns/field names as values in the combo box(one for select row filed and other one for select column filed in the report) when u select items in that combo boxes and press a Button then selected items should go to the crosstab query as parameters and execute the query. upto that i have done my coding part but...
2
5625
by: hwalker | last post by:
Hello all. Long time reader, first time poster :) I'm creating a repot that shows "the last two weeks of data the next 6 weeks of data, week over week". So, I have a calculated field called "Week number" as the column header in a crosstab and then in the criteria for that field I put: DateDiff("ww",#12/22/2007#,Now(),0,#12/22/2007#)-2 DateDiff("ww",#12/22/2007#,Now(),0,#12/22/2007#)-1 DateDiff("ww",#12/22/2007#,Now(),0,#12/22/2007#)...
0
8740
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8657
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9094
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8946
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7835
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5913
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4415
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2411
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2044
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.