SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Hitting the limit varchar(8000)
 New Topic  Topic Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

sg2255551
Constraint Violating Yak Guru

272 Posts

Posted - 07/24/2006 :  23:42:23  Show Profile
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!

Australia
4970 Posts

Posted - 07/25/2006 :  00:13:25  Show Profile  Visit Merkin's Homepage
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69553

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Topic Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000