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