Hitting the limit varchar(8000)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Hitting the limit varchar(8000)

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-24 : 23:42:23

hi

I can't get this to work.

Drop Table #Mytable

create table #Mytable (TCode varchar(10), TransDa int, Amount int, LedType Varchar(15))
insert #Mytable
select '01', 2006001, 1000, 'Acutal' union all
select 'AB001', 2006001, 1100, 'Forecast' union all
select 'AB002', 2006001, 1020, 'Forecast' union all
select '02', 2006001, 1150, 'Acutal' union all
select 'AB001', 2006002, 1200, 'Forecast' union all
select 'AB001', 2006002, 1210, 'Forecast' union all
select 'AB003', 2006003, 1110, 'Forecast' union all
select 'con', 2006002, 2000, 'Acutal'

----------------------------------------------------------------------------------------

declare @s varchar(300)
declare @s1 varchar(300)

Set @s = ''
Set @s1 = ''

If Len(@s + ' case when TCode Like ''' + TCode +
''' and TransDa = ' + cast(TransDa as varchar(10)) + ' and LedType = ''Forecast''' + ' then Amount else 0 end as [' +
TCode + '-' + cast(TransDa as varchar(15)) + ']') > 300

Begin
Select @s1 = isnull(@s1 + ',' + char(13), '') + ' case when TCode Like ''' + TCode +
''' and TransDa = ' + cast(TransDa as varchar(10)) + ' and LedType = ''Forecast''' + ' then Amount else 0 end as [' +
TCode + '-' + cast(TransDa as varchar(15)) + ']'
from #Mytable where TCode like 'AB%' group by TCode, TransDa
end
else
Begin
select @s = isnull(@s + ',' + char(13), '') + ' case when TCode Like ''' + TCode +
''' and TransDa = ' + cast(TransDa as varchar(10)) + ' and LedType = ''Forecast''' + ' then Amount else 0 end as [' +
TCode + '-' + cast(TransDa as varchar(15)) + ']'
from #Mytable where TCode like 'AB%' group by TCode, TransDa

end

exec('select *,' + @s + ' ' + @s1 + ' from #Mytable order by TCode, TransDa')

------------------------------------------------------------

I have gone thru the link http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52274

Any help thank.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2006-07-25 : 00:13:25

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69553

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources