473,320 Members | 1,978 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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(sampleid, txtTaxa, fltCount)

SELECT dbo.tblsampledata.sampleid,
dbo.CBN_RecursTaxa(dbo.tblbenthic.organism_tsn, @Level, " ") AS Taxa,
SUM(dbo.tblbenthic.[count] /
dbo.tblsitedetail.numberofreps) AS SumCount
FROM dbo.tblstudylist INNER JOIN
dbo.tblsite ON dbo.tblstudylist.studyid =
dbo.tblsite.study_id INNER JOIN
dbo.tblsitedetail ON dbo.tblsite.siteid =
dbo.tblsitedetail.site_id INNER JOIN
dbo.tblsampledata ON
dbo.tblsitedetail.sitedetailsid = dbo.tblsampledata.sitedetails_id
INNER JOIN
dbo.tblbenthic ON dbo.tblsampledata.sampleid =
dbo.tblbenthic.sample_id INNER JOIN
dbo.iter_intlist_to_table(@StudyID) i ON
dbo.tblstudylist.studyid = i.number INNER JOIN
dbo.tblbenthictaxa ON dbo.tblbenthic.organism_tsn =
dbo.tblbenthictaxa.tsn
WHERE (dbo.tblsampledata.qaqc = 0) AND (dbo.tblsampledata.status =
2) AND (dbo.tblbenthictaxa.rank_id >= @Level)
GROUP BY
dbo.tblsampledata.sampleid,
dbo.CBN_RecursTaxa(dbo.tblbenthic.organism_tsn, @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)NOT 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 7486

"Tim Pascoe" <ti********@cciw.ca> wrote in message
news:19**************************@posting.google.c om...
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
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...
16
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...
2
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...
2
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...
3
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...
28
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...
3
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...
14
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
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.