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 difficulties

Hello All,
I hope you can help with this.
I have next kind of data in the database(80000 rows) and I
want to drop out the min and max values.
day number prod quantity
monday 411 12004 545
monday 411 12004 776
monday 411 12004 345
monday 411 12004 876
monday 411 12004 764
tuesday 411 12004 324
tuesday 411 12004 225
tuesday 411 12004 421
tuesday 411 12004 314
tuesday 411 12004 432
I want to get rid off: monday 411 12004 345
monday 411 12004 876
tuesday 411 12004 432
tuesday 411 12004 225
and so on.
Thanks!help
CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 INT,
col3 INT,
col4 INT
)
INSERT INTO #Test VALUES ('monday',1,12004,500)
INSERT INTO #Test VALUES ('monday',1,12004,200)
INSERT INTO #Test VALUES ('monday',1,12004,2)
INSERT INTO #Test VALUES ('tuesday',1,12004,50)
INSERT INTO #Test VALUES ('tuesday',1,12004,10)
INSERT INTO #Test VALUES ('tuesday',1,12004,100)
SELECT * FROM #Test
WHERE col4 =(SELECT MAX(col4)FROM #Test t
WHERE t.col1=#Test.col1)
OR col4=(SELECT MIN(col4)FROM #Test t
WHERE t.col1=#Test.col1)
ORDER BY col1
"help" <anonymous@.discussions.microsoft.com> wrote in message
news:041201c539dc$3f13d5a0$a601280a@.phx.gbl...
> Hello All,
> I hope you can help with this.
> I have next kind of data in the database(80000 rows) and I
> want to drop out the min and max values.
> day number prod quantity
> monday 411 12004 545
> monday 411 12004 776
> monday 411 12004 345
> monday 411 12004 876
> monday 411 12004 764
> tuesday 411 12004 324
> tuesday 411 12004 225
> tuesday 411 12004 421
> tuesday 411 12004 314
> tuesday 411 12004 432
> I want to get rid off: monday 411 12004 345
> monday 411 12004 876
> tuesday 411 12004 432
> tuesday 411 12004 225
> and so on.
> Thanks!
>|||Try,
use northwind
go
create table t (
[day] varchar(10),
number int,
prod int,
quantity int)
go
insert into t values('monday', 411, 12004, 545)
insert into t values('monday', 411, 12004, 776)
insert into t values('monday', 411, 12004, 345)
insert into t values('monday', 411, 12004, 876)
insert into t values('monday', 411, 12004, 764)
insert into t values('tuesday', 411, 12004, 324)
insert into t values('tuesday', 411, 12004, 225)
insert into t values('tuesday', 411, 12004, 421)
insert into t values('tuesday', 411, 12004, 314)
insert into t values('tuesday', 411, 12004, 432)
go
delete t
where
not exists (select * from t as a where a.[day] = t.[day] and a.number =
t.number and a.prod = t.prod and a.quantity < t.quantity)
or not exists (select * from t as a where a.[day] = t.[day] and a.number =
t.number and a.prod = t.prod and a.quantity > t.quantity)
go
select * from t
go
drop table t
go
AMB
"help" wrote:

> Hello All,
> I hope you can help with this.
> I have next kind of data in the database(80000 rows) and I
> want to drop out the min and max values.
> day number prod quantity
> monday 411 12004 545
> monday 411 12004 776
> monday 411 12004 345
> monday 411 12004 876
> monday 411 12004 764
> tuesday 411 12004 324
> tuesday 411 12004 225
> tuesday 411 12004 421
> tuesday 411 12004 314
> tuesday 411 12004 432
> I want to get rid off: monday 411 12004 345
> monday 411 12004 876
> tuesday 411 12004 432
> tuesday 411 12004 225
> and so on.
> Thanks!
>|||I understand that you want to delete rows having the minimum and
maximum values of Quantity for each combination of Day, Number and
Prod. Try:
DELETE FROM YourTable
WHERE quantity IN
((SELECT MIN(quantity)
FROM YourTable AS T
WHERE day = YourTable.day
AND number = YourTable.number
AND prod = YourTable.prod),
(SELECT MAX(quantity)
FROM YourTable AS T
WHERE day = YourTable.day
AND number = YourTable.number
AND prod = YourTable.prod))
Make sure you have a current backup and test this out before you delete
any actual data.
David Portas
SQL Server MVP
--|||Just curious what does the # sign mean in #test?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ePwR61dOFHA.2748@.TK2MSFTNGP09.phx.gbl...
> help
> CREATE TABLE #Test
> (
> col1 VARCHAR(10),
> col2 INT,
> col3 INT,
> col4 INT
> )
> INSERT INTO #Test VALUES ('monday',1,12004,500)
> INSERT INTO #Test VALUES ('monday',1,12004,200)
> INSERT INTO #Test VALUES ('monday',1,12004,2)
> INSERT INTO #Test VALUES ('tuesday',1,12004,50)
> INSERT INTO #Test VALUES ('tuesday',1,12004,10)
> INSERT INTO #Test VALUES ('tuesday',1,12004,100)
>
> SELECT * FROM #Test
> WHERE col4 =(SELECT MAX(col4)FROM #Test t
> WHERE t.col1=#Test.col1)
> OR col4=(SELECT MIN(col4)FROM #Test t
> WHERE t.col1=#Test.col1)
> ORDER BY col1
>
>
> "help" <anonymous@.discussions.microsoft.com> wrote in message
> news:041201c539dc$3f13d5a0$a601280a@.phx.gbl...
>|||Aaron
#--local temporary table
##--global temporary table
For more details please refer to the BOL
"Aaron" <kuya789@.yahoo.com> wrote in message
news:%23C3sxFeOFHA.3444@.tk2msftngp13.phx.gbl...
> Just curious what does the # sign mean in #test?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ePwR61dOFHA.2748@.TK2MSFTNGP09.phx.gbl...
>|||Hi,
Try this :
delete t
where quantity in (select min(quantity) from t group by [day], number, prod)
or quantity in (select max(quantity) from t group by [day], number, prod)
"help" wrote:

> Hello All,
> I hope you can help with this.
> I have next kind of data in the database(80000 rows) and I
> want to drop out the min and max values.
> day number prod quantity
> monday 411 12004 545
> monday 411 12004 776
> monday 411 12004 345
> monday 411 12004 876
> monday 411 12004 764
> tuesday 411 12004 324
> tuesday 411 12004 225
> tuesday 411 12004 421
> tuesday 411 12004 314
> tuesday 411 12004 432
> I want to get rid off: monday 411 12004 345
> monday 411 12004 876
> tuesday 411 12004 432
> tuesday 411 12004 225
> and so on.
> Thanks!
>