473,549 Members | 3,105 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Dynamic Cross-Tab Query too long?

I am using the Dynamic Cross-Tab code supplied in an article from SQL
Server Magazine (http://www.winnetmag.com/SQLServer/A...08/15608.html).
I modified the script to generate a temp table inside the stored
procedure, and then use this temp table as the source for the
cross-tab. However, the problem seems to be that the dynamic SQL
string generated by the script is longer than what can be stored in
the @SQL variable. The Cross-tab works great, so long as the amount of
data to be pivoted is small.

Is there any way around this? E.g. a User defined type, or another
data type which can store more characters?

Thanks,

Tim
CREATE procedure CBN_CrossTab

@StudyID varchar(100), --Model ID passed from web app - Only one model
can be selected
@Level int --The level to which the taxonomy should be rolled up

As

DECLARE

@Table as sysname, --Table to crosstab
@OnRows as nvarchar(128), --Groupuing key values (on rows)
@OnRowsAlias as sysname, --Alias for grouping cloumn
@OnCols as nvarchar(128), --destination columns (on columns)
@SumCol as sysname, --data cels
@SQL AS varchar(8000), -- String to hold generated SQL String
@NEWLINE as char(1) --Holds the New Line Character for the code

SET @OnRowsAlias = Null
SET @SumCol = Null
SET @NEWLINE = CHAR(10)

-- Generate the Temp table for the taxa and counts
CREATE TABLE #RefOrganisms (sampleid int, txtTaxa varchar(75),
fltCount float)

INSERT INTO #RefOrganisms(s ampleid, txtTaxa, fltCount)

SELECT dbo.tblsampleda ta.sampleid,
dbo.CBN_RecursT axa(dbo.tblbent hic.organism_ts n, @Level, " ") AS Taxa,
SUM(dbo.tblbent hic.[count] /
dbo.tblsitedeta il.numberofreps ) AS SumCount
FROM dbo.tblstudylis t INNER JOIN
dbo.tblsite ON dbo.tblstudylis t.studyid =
dbo.tblsite.stu dy_id INNER JOIN
dbo.tblsitedeta il ON dbo.tblsite.sit eid =
dbo.tblsitedeta il.site_id INNER JOIN
dbo.tblsampleda ta ON
dbo.tblsitedeta il.sitedetailsi d = dbo.tblsampleda ta.sitedetails_ id
INNER JOIN
dbo.tblbenthic ON dbo.tblsampleda ta.sampleid =
dbo.tblbenthic. sample_id INNER JOIN
dbo.iter_intlis t_to_table(@Stu dyID) i ON
dbo.tblstudylis t.studyid = i.number INNER JOIN
dbo.tblbenthict axa ON dbo.tblbenthic. organism_tsn =
dbo.tblbenthict axa.tsn
WHERE (dbo.tblsampled ata.qaqc = 0) AND (dbo.tblsampled ata.status =
2) AND (dbo.tblbenthic taxa.rank_id >= @Level)
GROUP BY
dbo.tblsampleda ta.sampleid,
dbo.CBN_RecursT axa(dbo.tblbent hic.organism_ts n, @Level, " ")

-- Identify the Temp table info for the CrossTab

SELECT @Table = '#RefOrganisms'
SELECT @OnRows = 'sampleid'
SELECT @OnCols = 'txtTaxa'
SELECT @OnRowsAlias = Null
SELECT @SumCol = 'fltCount'

--STEP1 BEGININNING OF SQL STRING
SET @sql = 'SELECT'+ @newline +' '+ @onrows +
CASE
WHEN @ONROWSALIAS IS NOT NULL THEN ' AS ' + @ONROWSALIAS
ELSE ''
END

CREATE TABLE #KEYS(KEYVALUE NVARCHAR(100)NO T NULL PRIMARY KEY)

DECLARE @KEYSSQL AS VARCHAR (1000)

SET @KEYSSQL = 'INSERT INTO #KEYS ' + 'SELECT DISTINCT CAST(' +
@ONCOLS + '
AS NVARCHAR(100)) ' + 'FROM ' + @TABLE
EXEC (@KEYSSQL)

DECLARE @KEY AS NVARCHAR(100)

SELECT @KEY = MIN(KEYVALUE) FROM #KEYS

WHILE @KEY IS NOT NULL
BEGIN
SET @SQL = @SQL + ' ,'+ @NEWLINE +
' SUM(CASE CAST(' + @ONCOLS +
' AS NVARCHAR(100))' + @NEWLINE +
' WHEN N''' + @KEY +
''' THEN '+ CASE
WHEN @SUMCOL IS NULL THEN '1'
ELSE @SUMCOL
END + @NEWLINE +
' ELSE 0' + @NEWLINE +
' END) AS [' + @KEY + ']'
SELECT @KEY = MIN(KEYVALUE) FROM #KEYS
WHERE KEYVALUE > @KEY
END

SET @SQL = @SQL + @NEWLINE +
'FROM ' + @TABLE + @NEWLINE +
'GROUP BY ' + @ONROWS + @NEWLINE +
'ORDER BY ' + @ONROWS

PRINT @SQL --+ @NEWLINE --FOR DEBUG
EXEC (@SQL)
GO
Jul 20 '05 #1
1 7501

"Tim Pascoe" <ti********@cci w.ca> wrote in message
news:19******** *************** ***@posting.goo gle.com...
I am using the Dynamic Cross-Tab code supplied in an article from SQL
Server Magazine (http://www.winnetmag.com/SQLServer/A...08/15608.html). I modified the script to generate a temp table inside the stored
procedure, and then use this temp table as the source for the
cross-tab. However, the problem seems to be that the dynamic SQL
string generated by the script is longer than what can be stored in
the @SQL variable. The Cross-tab works great, so long as the amount of
data to be pivoted is small.

Is there any way around this? E.g. a User defined type, or another
data type which can store more characters?

Thanks,

Tim


<snip>

One possibility is to use several variables, then execute them like this:

EXEC(@sql1 + @sql2 + @sql3 + ...)

See EXECUTE in Books Online for more information. There are data types which
hold more than 8000 characters (text, ntext), but they cannot be used with
EXEC().

Simon
Jul 20 '05 #2

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

Similar topics

3
2005
by: jonathan | last post by:
hey all, I'd like to implement an application that is truly dynamic: the components are not only just known at runtime, but also the components are unknown at runtime: ie: some don't even ship with the application. When a new component is shipped, the application 'picks up' that component and drops it in to the interface (in this case a...
16
4326
by: 4Space | last post by:
I've hit something of a snag. Problem: In a DSP application we have data profiles with a varying number of points. We have a number of Discrete Fourier transforms that are optimised for a specific number of points. Currently, any client of these DFT classes must switch on the number of points in the profile and instanciate the correct...
2
2568
by: Martin Doyle | last post by:
Ok, I'm building a JS-based limitless-sublevel dynamic menu and am making it cross browser as well - 3 packs of aspirin so far and counting ;) I'm having a weird rendering problem using Opera 7.51, even though it displays fine in Mozilla 1.6, Firefox 0.9, Netscape 7.1 and Internet Explorer 6.0 Hope someone can help!
2
8827
by: klh | last post by:
We use DB2 Connect v 7.2 FP7 in Windows NT hitting a OS/390 DB2 v7.1 database. We have a Websphere (java) application that issues dynamic SQL. Most of the time when we issue dynamic SQL SELECT statements, like through a DB2 command window, the command will be processed using a package like SQLLF000 which uses an isolation level of Cursor...
3
2665
by: Mukesh | last post by:
sir, i am developing a database, which will store the users profile both personal and professional which includes the address, telephone, gender and etc. in my main table i have created a column of xml data type. i was successful in inserting the static value inside my table.by following code:--- UPDATE docs
28
2554
by: sturlamolden | last post by:
On Monday Microsoft announced a new runtime for dynamic languages, which they call "DLR". It sits on top of the conventional .NET runtime (CLR) and provides services for dynamically typed languages like Python or Lisp (thus the cross-posting). Apparently is is distributed under a BSD-like open-source license. I am curious to know how it...
3
2614
by: EnigmaticSource | last post by:
Currently, I am designing a site using CSS driven vertical menus, it works well in everything but MSIE. The menus seem to work well enough, except that they float behind the images, but above the text. The problem does not occur in Firefox, Konqueror, or Opera. I'm a bit lost for what the cause could be. Demonstration URL:...
14
5387
by: asdf | last post by:
I have a python script whose output i want to dynamically display on a webpage which will be hosted using Apache. How do I do that? thanks
0
7520
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...
0
7809
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...
0
6041
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...
1
5368
isladogs
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...
0
5088
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...
0
3498
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...
0
3480
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1936
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1058
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.