declare @sql varchar(8000)set @sql=''
declare @swhere varchar(8000)set @swhere=' (1=1)'
select @sql=@sql+'sum(case dbo.num_h(F_Specs,F_Material) when '''+ aa
+''' then F_StoreNumber end) as '''+aa
+''',' from (select distinct dbo.num_h(F_Specs,F_Material) as aa from v_PtStore
WHERE (dbo.v_PtStore.F_PtNo IN (SELECT dbo.v_AllFieldInStore.F_PtNo
FROM dbo.v_AllFieldInStore WHERE (dbo.v_AllFieldInStore.F_Type = '管材')))
AND (F_StoreNumber <> 0) and (1=1) ) a 要把红色部分就是 (1=1) 用@swhere来代替....
select @NewSql=
'select ''sum(case dbo.num_h(F_Specs,F_Material) when '''
+' +aa+ '
+''' then F_StoreNumber end) as '''
+' +aa+ '
+''','''
+'from (select distinct dbo.num_h(F_Specs,F_Material) as aa from v_PtStore
WHERE (dbo.v_PtStore.F_PtNo IN (SELECT dbo.v_AllFieldInStore.F_PtNo
FROM dbo.v_AllFieldInStore WHERE (dbo.v_AllFieldInStore.F_Type = ''管材'')))
AND (F_StoreNumber <> 0) and (1=1) ) a '
DECLARE rs CURSOR LOCAL SCROLL FOR
exec(@NewSql) --这里不允许这样写...郁闷死
OPEN rs
FETCH NEXT FROM rs INTO @temptb
while @@FETCH_STATUS = 0
BEGIN
select @sql=@sql+@temptb
FETCH NEXT FROM rs INTO @temptb
END
CLOSE rs
print @sql
咋办?
declare @sql1 varchar(8000)set @sql1=''
declare @swhere varchar(8000)set @swhere=' (1=1)'SET @SQL1='select distinct dbo.num_h(F_Specs,F_Material) as aa from v_PtStore
WHERE (dbo.v_PtStore.F_PtNo IN (SELECT dbo.v_AllFieldInStore.F_PtNo
FROM dbo.v_AllFieldInStore WHERE (dbo.v_AllFieldInStore.F_Type = ''管材'')))
AND (F_StoreNumber <> 0) and WHERE '+@swhere+') a'
CREATE TABLE #T(AA INT)
INSERT #T EXEC(@SQL1)select @sql=@sql+'sum(case dbo.num_h(F_Specs,F_Material) when '''+ aa
+''' then F_StoreNumber end) as '''+aa +''',' FROM #T
http://blog.csdn.net/roy_88/archive/2007/11/09/1875264.aspx
select @NewSql=
'DECLARE rs CURSOR GLOBAL FOR
select ''sum(case dbo.num_h(F_Specs,F_Material) when '''
+' +aa+ '
+''' then F_StoreNumber end) as '''
+' +aa+ '
+''','''
+'from (select distinct dbo.num_h(F_Specs,F_Material) as aa from v_PtStore
WHERE (dbo.v_PtStore.F_PtNo IN (SELECT dbo.v_AllFieldInStore.F_PtNo
FROM dbo.v_AllFieldInStore WHERE (dbo.v_AllFieldInStore.F_Type = ''管材'')))
AND (F_StoreNumber <> 0) and (1=1) ) a ' exec(@NewSql) OPEN rs
FETCH NEXT FROM rs INTO @temptb
while @@FETCH_STATUS = 0
BEGIN
select @sql=@sql+@temptb
FETCH NEXT FROM rs INTO @temptb
END
CLOSE rs
DEALLOCATE rs
print @sql