467,877 Members | 1,079 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,877 developers. It's quick & easy.

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
  • viewed: 7225
Share:
1 Reply

"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.

Similar topics

3 posts views Thread by jonathan | last post: by
2 posts views Thread by Martin Doyle | last post: by
3 posts views Thread by Mukesh | last post: by
28 posts views Thread by sturlamolden | last post: by
3 posts views Thread by EnigmaticSource | last post: by
14 posts views Thread by asdf | last post: by
reply views Thread by jack112 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.