By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,710 Members | 1,601 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,710 IT Pros & Developers. It's quick & easy.

Dynamic Cross-Tab Query too long?

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a

"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 discussion thread is closed

Replies have been disabled for this discussion.