QUERY Exceeding variable max length

Hi,

A query is exceeding the length of varchar and nvarchar variable.
Because I'm picking the data from each record from table and giving it
to the query.

suggest me some way to do it.

sample query:

SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
FROM (SELECT Year,
SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS
Q1,
SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS
Q2,
SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS
Q3,
SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
FROM Pivot1 AS P
GROUP BY P.Year) AS P1
GO

--> even the P.QUARTER ... FIELD NAME IS BEING GENERATED
DYNAMICALLY.

MY QUERY IS EXCEEDING VARCHAR AND NVARCHAR LIMIT.

THANX IN ADV.One option is to split up the VARCHAR variable into multiple variables &
concatenate them in your EXEC like:
EXEC( @.v1 + @.v2 + ...)

Also, you can avoid all these hazzles, if you bring back the resultset to
the client application & do the pivoting on the front end.

--
- Anith
( Please reply to newsgroups only )

query analyzer2

hey all,
if i declare a variable and set it is there a way i can get it to display on
the messages window or the grid?
thanks,
rodchar
The grid:
SELECT @.varname
The messages windows
PRINT @.varname
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:12D27260-BADD-4F0F-BBCB-1E1ABC2C17C4@.microsoft.com...
> hey all,
> if i declare a variable and set it is there a way i can get it to display on
> the messages window or the grid?
> thanks,
> rodchar
|||sorry bout that, i figured it out
declare @.myVar datetime
set @.myVar = getdate()
select @.myVar
thanks,
rodchar
"rodchar" wrote:

> hey all,
> if i declare a variable and set it is there a way i can get it to display on
> the messages window or the grid?
> thanks,
> rodchar
|||cool i didn't know about the print function.
"Tibor Karaszi" wrote:

> The grid:
> SELECT @.varname
> The messages windows
> PRINT @.varname
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:12D27260-BADD-4F0F-BBCB-1E1ABC2C17C4@.microsoft.com...
>
|||Describe the procedure that will let the user tell Windows to open any file with an .RPT extension with Notepad.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Start Notepad, File, Open, Files of type: all files, select the file, OK. You can also change the
file extension in QA, Tools, Options.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Glennette Adams" <gadams6822@.vccs.edu> wrote in message
news:eSfm$9X2FHA.2472@.TK2MSFTNGP12.phx.gbl...
> Describe the procedure that will let the user tell Windows to open any file with an .RPT extension
> with Notepad.
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...