473,897 Members | 2,313 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 5236
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
17692
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
2308
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
3346
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
3601
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
2950
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
5076
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
17178
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
2924
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
2328
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
5638
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
9990
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
11250
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10850
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...
1
10941
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10478
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
9656
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
7185
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();...
2
4293
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3300
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.