declare @sql varchar(3000)
set @sql=''
set @sql='select FItemID'
select @sql=@sql+',(case FName when '''+FName+''' then FAuxQty)['+ FName+']'
from (select distinct FName from 表)a
set @sql=@sql+',FStockID from 表'
exec(@sql)
set @sql=''
set @sql='select FItemID'
select @sql=@sql+',(case FName when '''+FName+''' then FAuxQty)['+ FName+']'
from (select distinct FName from 表)a
set @sql=@sql+',FStockID from 表'
exec(@sql)
(
FItemID varchar(3),
FName nvarchar(100),
FStockID int,
FDeptID int,
FAuxQty numeric(14,2)
)
declare @sql varchar(800)
set @sql = 'select FItemID'
select @sql =@sql+ ',isnull(sum(case when FName= '''+FName+''' then FAuxQty end),0) ['+FName+']'
from (select distinct FName from 记录表1 ) as a
select @sql = @sql+' ,FStockID from 记录表1 group by FItemID,FStockID'
exec (@sql)FItemID 包装车间 生产部 FStockID
337 .00 1000.00 598
339 200.00 .00 598
337 1000.00 .00 624
set @sql = 'select FItemID'
select @sql = @sql + ',isnull(sum(case FName when '''+FName+''' then FAuxQty end),0) ['+FName+']'
from (select distinct FName from test1 ) as a
select @sql = @sql+',FStockID from test1 group by FItemID,FStockID order by FItemID,FStockID'
exec(@sql)FItemID 包装车间 生产部 FStockID
------- ----------- -------------- -----------
337 0 1000 98
337 1000 0 624
339 200 0 598
declare @sql varchar(3000)
set @sql=''
set @sql='select FItemID'
select @sql=@sql+',sum(case FName when '''+FName+''' then FAuxQty else 0 end)['+ FName+']'
from (select distinct FName from 表)a
set @sql=@sql+',FStockID from 表 group by FItemID,FStockID'
exec(@sql)
set @sql=''
set @sql='select FItemID'
select @sql=@sql+',(case FName when '''+FName+''' then FAuxQty)['+ FName+']'
from (select distinct FName from 表)a
set @sql=@sql+',FStockID from 表'
exec(@sql)
如果动态字符串超过80000.
参考:
http://community.csdn.net/Expert/topic/3746/3746357.xml?temp=.9807855
(
FName varchar(100)
)CREATE PROCEDURE usp_GetSalaryList AS
------------build Dynamic SQL sentence--Declare Variable for Cursor
declare @s_ColSet_userName varchar(30)declare @s_ColSet_SQL nvarchar(4000) --SQL表达式
declare @s_ColSet_GroupFun nvarchar(1000) --分组函数
--Declare Cursor
declare cur_GetSalaryItem Cursor
for select fName from FNameTable order by fName
select @s_ColSet_SQL = ""
select @s_ColSet_GroupFun = ""open cur_GetSalaryItemfetch next from cur_GetSalaryItem into @s_ColSet_userNamewhile (@@rowcount > 0)
begin
if (Len(@s_ColSet_SQL) >0)
select @s_ColSet_SQL = @s_ColSet_SQL + "," select @s_ColSet_SQL = @s_ColSet_SQL + "(case when FName=" + @s_ColSet_userName + " then iPrice else 0 end) As " + @s_ColSet_userName
if (Len(@s_ColSet_GroupFun) > 0)
select @s_ColSet_GroupFun = @s_ColSet_GroupFun + ","
select @s_ColSet_GroupFun = @s_ColSet_GroupFun + "sum("+@s_ColSet_userName+") As " + @s_ColSet_userName
fetch next from cur_GetSalaryItem into @s_ColSet_userNameend
close cur_GetSalaryItemif (len(@s_ColSet_SQL) > 0 ) begin
select @s_ColSet_SQL = "select FItemID," + @s_ColSet_GroupFun + " from (select FItemID," + @s_ColSet_SQL+ ",FStockID
from 记录表) A group by FItemID"
exec(@s_ColSet_SQL)
--输出结果集 编号,lll sss zzz.....end
--else
--返回结构
GO