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
1  5230 
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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
|
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...
|
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...
|
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:
|
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
| | |
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...
|
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.
|
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...
|
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...
|
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#)...
|
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...
| | |
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,...
|
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...
|
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...
|
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...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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();...
| | |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
