I'm attempting to modify some Crosstab generating code, and I need some
advice/examples.
Currently, the code uses a single string variable to store the
dynamically generated query ( www.johnmacintyre.ca). The problem is that
I am trying to pivot biological taxonomy information, and may end up
with a table containing over 200 columns. This takes the dynamic string
well over the 8000char limit for variables. From my understanding, the EXEC() command does not have the 8000char
limit if the execution string is broken into chunks, and concatenated
e.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need is
to:
1) start a counter at the beginining of the dynamic generation
2) append the counter value to the end of a string variable name
3) DECLARE the new variable and attach that loop cycle of text to it,
or attach each chunk of characters < 8000
4) build the EXEC() string by concatenating each dynamic varible
Can this be done? Should it be done? Is there a better way to address
this type of problem?
Thanks for any ideas or insights
Tim Pascoe 5 6168
>> Can this be done? <<
Maybe, maybe not. Should it be done? <<
No, this is a mis-use of SQL.
Is there a better way to address this type of problem? <<
Crosstabs (thank you for not calling them "pivot tables") are a report
and not a query. you ought to use a report tool and not SQL for this
kind of job.
Hi
You may want to look at previous posts regarding crosstab queries, as Joe
says it is better to do this in the reporting tool such as RAC http://www.rac4sql.net/ and others.
Here are a few links you may want to read: http://www.windowsitpro.com/SQLServe...608/15608.html http://support.microsoft.com/default...;EN-US;q175574 http://www.sqlteam.com/item.asp?ItemID=2955
John
<ti********@cci w.ca> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.com... I'm attempting to modify some Crosstab generating code, and I need some advice/examples.
Currently, the code uses a single string variable to store the dynamically generated query (www.johnmacintyre.ca). The problem is that I am trying to pivot biological taxonomy information, and may end up with a table containing over 200 columns. This takes the dynamic string well over the 8000char limit for variables.
From my understanding, the EXEC() command does not have the 8000char limit if the execution string is broken into chunks, and concatenated e.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need is to:
1) start a counter at the beginining of the dynamic generation 2) append the counter value to the end of a string variable name 3) DECLARE the new variable and attach that loop cycle of text to it, or attach each chunk of characters < 8000 4) build the EXEC() string by concatenating each dynamic varible
Can this be done? Should it be done? Is there a better way to address this type of problem?
Thanks for any ideas or insights
Tim Pascoe
RAC was my next point of investigation. The problem is that I'm not
actually producing a report of the data (although I agree this is
generally what crosstabs are for). Instead, the data is required in
this format so it can be fed into statistical software, which requires
the crosstabulated structure. I'll look into RAC and the other sites
you listed.
Thanks.
John Bell wrote: Hi You may want to look at previous posts regarding crosstab queries, as
Joe says it is better to do this in the reporting tool such as RAC http://www.rac4sql.net/ and others.
Here are a few links you may want to read: http://www.windowsitpro.com/SQLServe...608/15608.html http://support.microsoft.com/default...;EN-US;q175574
http://www.sqlteam.com/item.asp?ItemID=2955
John
<ti********@cci w.ca> wrote in message news:11******** *************@f 14g2000cwb.goog legroups.com... I'm attempting to modify some Crosstab generating code, and I need
some advice/examples.
Currently, the code uses a single string variable to store the dynamically generated query (www.johnmacintyre.ca). The problem is
that I am trying to pivot biological taxonomy information, and may end
up with a table containing over 200 columns. This takes the dynamic
string well over the 8000char limit for variables.
From my understanding, the EXEC() command does not have the
8000char limit if the execution string is broken into chunks, and
concatenated e.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need
is to:
1) start a counter at the beginining of the dynamic generation 2) append the counter value to the end of a string variable name 3) DECLARE the new variable and attach that loop cycle of text to
it, or attach each chunk of characters < 8000 4) build the EXEC() string by concatenating each dynamic varible
Can this be done? Should it be done? Is there a better way to
address this type of problem?
Thanks for any ideas or insights
Tim Pascoe
Hi
You may want to consider Analysis services then?
John
<ti********@cci w.ca> wrote in message
news:11******** *************@c 13g2000cwb.goog legroups.com... RAC was my next point of investigation. The problem is that I'm not actually producing a report of the data (although I agree this is generally what crosstabs are for). Instead, the data is required in this format so it can be fed into statistical software, which requires the crosstabulated structure. I'll look into RAC and the other sites you listed.
Thanks.
John Bell wrote: Hi You may want to look at previous posts regarding crosstab queries, as Joe says it is better to do this in the reporting tool such as RAC http://www.rac4sql.net/ and others.
Here are a few links you may want to read: http://www.windowsitpro.com/SQLServe...608/15608.html http://support.microsoft.com/default...;EN-US;q175574
http://www.sqlteam.com/item.asp?ItemID=2955
John
<ti********@cci w.ca> wrote in message news:11******** *************@f 14g2000cwb.goog legroups.com... > I'm attempting to modify some Crosstab generating code, and I need some > advice/examples. > > Currently, the code uses a single string variable to store the > dynamically generated query (www.johnmacintyre.ca). The problem is that > I am trying to pivot biological taxonomy information, and may end up > with a table containing over 200 columns. This takes the dynamic string > well over the 8000char limit for variables. > >>From my understanding, the EXEC() command does not have the 8000char > limit if the execution string is broken into chunks, and concatenated > e.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need is > to: > > 1) start a counter at the beginining of the dynamic generation > 2) append the counter value to the end of a string variable name > 3) DECLARE the new variable and attach that loop cycle of text to it, > or attach each chunk of characters < 8000 > 4) build the EXEC() string by concatenating each dynamic varible > > Can this be done? Should it be done? Is there a better way to address > this type of problem? > > Thanks for any ideas or insights > > Tim Pascoe >
(ti********@cci w.ca) writes: I'm attempting to modify some Crosstab generating code, and I need some advice/examples.
Currently, the code uses a single string variable to store the dynamically generated query (www.johnmacintyre.ca). The problem is that I am trying to pivot biological taxonomy information, and may end up with a table containing over 200 columns. This takes the dynamic string well over the 8000char limit for variables.
From my understanding, the EXEC() command does not have the 8000char limit if the execution string is broken into chunks, and concatenated e.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need is to:
1) start a counter at the beginining of the dynamic generation 2) append the counter value to the end of a string variable name 3) DECLARE the new variable and attach that loop cycle of text to it, or attach each chunk of characters < 8000 4) build the EXEC() string by concatenating each dynamic varible
Can this be done? Should it be done? Is there a better way to address this type of problem?
I think it can be done, but I would not like to do it. You would have
to generate dynamic SQL which in its turn generates the dynamic SQL
that executes the query.
It may be a better alternative to use a client language to generate the
SQL. If you use a language like Perl or Visual Basic which has unlimited
strings, you are saved the restriction of varchar(8000). Note that all
that you would bring to the client would be the meta data needed to
form the SQL statement.
In SQL 2005 there is a new data type varchar(MAX) which is akin to text,
but works more like varchar, and thus in SQL 2005 you would also be saved
from the varchar(8000) restriction. SQL 2005 is currently in beta, with
release planned next year.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.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: rick |
last post by:
Noob problem. I prefer to keep all my scripts in an external '.js' file.
I am currently loading the external '.js' file from the header. Problem is I
would like to declare
a global variable in the external file, but I keep getting an error about
the object does not exist.
Can someone tell me where or how to declare a global variable in an external
file that is
available after the page is loaded.
|
by: James A. Donald |
last post by:
I am contemplating getting into Python, which is used by engineers I
admire - google and Bram Cohen, but was horrified to read
"no variable or argument declarations are necessary."
Surely that means that if I misspell a variable name, my program will
mysteriously fail to work with no error message.
If you don't declare variables, you can inadvertently re-use an
variable used in an enclosing context when you don't intend to, or
|
by: Ishaan |
last post by:
Hi,
Is it possible to declare a variable dynamically?
Anwar
--
Software engineer
Petroleum Development Oman
|
by: ankurdave |
last post by:
Is it possible to declare a class member variable in the constructor?
For example,
class SomeClass
{
public:
SomeClass()
{ int SomeArray; }
}
|
by: redefined.horizons |
last post by:
I would like to have an array declaration where the size of the array
is dependent on a variable. Something like this:
/* Store the desired size of the array in a variable named
"array_size". */
unsigned short int array_size = 25;
/*Declare an array named "numbers" using the variable initialized
above. */
unsigned short int numbers;
| |
by: ares.lagae |
last post by:
- I have a typelist and I want to declare a member variable for each
of the types. How can I do that?
E.g. I have the typelist "typedef boost::mpl::vector<int, float>
types;" and I want to declare member variables with type "int" and
"float".
- I have a typelist and I want to declare a variable based on each of
the types types. How can I do that?
E.g. I have the typelist "typedef boost::mpl::vector<int, float>
types;" and I want to...
|
by: roamnet |
last post by:
hi
i created database file with .mdf extention ,sql server as a source
and use grid view to display data
there're no problem in data retrieve and display,but i want to edit it
or insert new records
there is an error "Incorrect syntax near '-'.
Must declare the scalar variable "@UserName".
I worked out in design view,code is automatically generated.Iam not
able fix the error.
Iam working with Visual Web Developer-2005 Express Edition
|
by: Andrea Raimondi |
last post by:
Hello peers!
I'm working on this application and I'm in need for some thoughtful advice
:-p
I have an SQLDataSource with params, select, etc.
One of my params is the table name, which can be programmatically set, this
is necessary because I may have a
simple table name or a union, hence I got to pick the correct one!
Unfortunately, I can't bind this parameter to a control, session or
|
by: macneed |
last post by:
how can i declare a variable inside a if case?
if(today != recordday)
{
string NewRecordOfToday;
}
.. other work...
..
..
if(today != recordday)
|
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: 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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
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...
| |