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.
Author |
Topic |
sg2255551
Constraint Violating Yak Guru
274 Posts |
Posted - 2006-07-25 : 00:08:28
|
Sorry i posted to the wrong category in Transact SQL. It should be here the Devloper category.-----------------------------------------------------------hiI can't get this to work.Drop Table #Mytablecreate table #Mytable (TCode varchar(10), TransDa int, Amount int, LedType Varchar(15))insert #Mytableselect '01', 2006001, 1000, 'Acutal' union allselect 'AB001', 2006001, 1100, 'Forecast' union allselect 'AB002', 2006001, 1020, 'Forecast' union allselect '02', 2006001, 1150, 'Acutal' union allselect 'AB001', 2006002, 1200, 'Forecast' union allselect 'AB001', 2006002, 1210, 'Forecast' union allselect 'AB003', 2006003, 1110, 'Forecast' union allselect '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)) + ']') > 300BeginSelect @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, TransDaendelseBeginselect @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, TransDaendexec('select *,' + @s + ' ' + @s1 + ' from #Mytable order by TCode, TransDa')------------------------------------------------------------I have gone thru the link http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52274Thanks |
|
Kristen
Test
22859 Posts |
Posted - 2006-07-25 : 01:41:55
|
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)) + ']') > 300won't work because it does not reference any table to provide a value for TCode, TransDa etc.Better to start with a description of the problem you are trying to solve pleaseKristen |
|
|
sg2255551
Constraint Violating Yak Guru
274 Posts |
Posted - 2006-07-25 : 02:17:40
|
basically is to find out whether it exceed the varchar length and if does it will continue to the next declare variable:----------------------------------------------------------create table #Mytable (TCode varchar(10), TransDa int, Amount int, LedType Varchar(15))insert #Mytableselect '01', 2006001, 1000, 'Acutal' union allselect 'AB001', 2006001, 1100, 'Forecast' union allselect 'AB002', 2006001, 1020, 'Forecast' union allselect '02', 2006001, 1150, 'Acutal' union allselect 'AB001', 2006002, 1200, 'Forecast' union allselect 'AB001', 2006002, 1210, 'Forecast' union allselect 'AB003', 2006003, 1110, 'Forecast' union allselect 'con', 2006002, 2000, 'Acutal'----------------------------------------------------------------------------------------declare @s varchar(300)declare @s1 varchar(300)Set @s = ''Set @s1 = ''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, TransDaIf Len(@S) > 290BeginSelect @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, TransDaendelseBeginselect @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, TransDaendPrint Len(@s)Print Len(@s1)exec('select *,' + @s + ' ' + @s1 + ' from #Mytable order by TCode, TransDa')------------------------------------------------------------------Thanks |
|
|
sg2255551
Constraint Violating Yak Guru
274 Posts |
Posted - 2006-07-25 : 03:52:16
|
I know basic syntax but not the deeper things. Any help please. Thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-25 : 04:41:06
|
"basically is to find out whether it exceed the varchar length and if does it will continue to the next declare variable"Sorry, but that isn't the problem you are trying to solve, that is the brick wall you have hit with your proposed solution!Its the overall problem I need to understand please.Kristen |
|
|
sg2255551
Constraint Violating Yak Guru
274 Posts |
Posted - 2006-07-25 : 05:09:27
|
hiOk. I have a large table with 600,000 rows of record and when i run this demo code: presuming @s is delare as varchar(8000). This code below is a simulation of the situation.create table #Mytable (TCode varchar(10), TransDa int, Amount int, LedType Varchar(15))insert #Mytableselect '01', 2006001, 1000, 'Acutal' union allselect 'AB001', 2006001, 1100, 'Forecast' union allselect 'AB002', 2006001, 1020, 'Forecast' union allselect '02', 2006001, 1150, 'Acutal' union allselect 'AB001', 2006002, 1200, 'Forecast' union allselect 'AB001', 2006002, 1210, 'Forecast' union allselect 'AB003', 2006003, 1110, 'Forecast' union allselect 'con', 2006002, 2000, 'Acutal'declare @s varchar(300)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, TransDaPrint Len(@s)exec('select *,' + @s + ' ' + @s1 + ' from #Mytable order by TCode, TransDa')------------------------------------------------------------The length of @s is 7423 in my real table. So, right now i am trying to solve the limit of varchar(8000) if not as the number of rows keep increasing, i would definitely hit the 8000 limit.I went thru the post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52274 but after trying many times on my PC. I still get error. I guess i am not that creative in solving script problem. I can still further explain if you need to.Thanks. |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-25 : 05:59:13
|
OK, now I can see what you are trying to doYou are trying to make a cross tabI suggest you have a look athttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Dynamic+Cross,Tabs,Pivot+Table,,Pivot+TablesThe solution to the wall you have hit is:create table #Mytable (TCode varchar(10), TransDa int, Amount int, LedType Varchar(15))insert #Mytableselect '01', 2006001, 1000, 'Acutal' union allselect 'AB001', 2006001, 1100, 'Forecast' union allselect 'AB002', 2006001, 1020, 'Forecast' union allselect '02', 2006001, 1150, 'Acutal' union allselect 'AB001', 2006002, 1200, 'Forecast' union allselect 'AB001', 2006002, 1210, 'Forecast' union allselect 'AB003', 2006003, 1110, 'Forecast' union allselect 'con', 2006002, 2000, 'Acutal'DECLARE @SQLDynamic1 varchar(8000), @SQLDynamic2 varchar(8000), @SQLDynamic3 varchar(8000)SELECT @SQLDynamic3 = CASE WHEN LEN(@SQLDynamic1) > 7000 THEN @SQLDynamic2 ELSE @SQLDynamic3 END, @SQLDynamic2 = CASE WHEN LEN(@SQLDynamic1) > 7000 THEN @SQLDynamic1 ELSE @SQLDynamic2 END, @SQLDynamic1 = CASE WHEN LEN(@SQLDynamic1) > 7000 THEN '' ELSE @SQLDynamic1 END, @SQLDynamic1 = isnull(@SQLDynamic1 + ',' + 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, TransDaexec('select *,' + @SQLDynamic3 + ' ' + @SQLDynamic2 + ' ' + @SQLDynamic1 + ' from #Mytable order by TCode, TransDa')DROP TABLE #Mytable GO Add more @SQLDynamic9999 vairables as necessary to contain the overall size. The assumption is that the string generated for each iteration will NOT exceed 1,000 characters (the difference between the 8,000 character declaration and the 7,000 size test). Adjust if required ....Kristen |
|
|
sg2255551
Constraint Violating Yak Guru
274 Posts |
Posted - 2006-07-25 : 09:34:25
|
Thank you so much. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-25 : 10:19:58
|
Or you could build the string up in a text column of a single row temp table. Then use dynamic sql to split up the string into variables and execute it.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|