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.

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 )