DECLARE @sql varchar(8000)
SET @sql = 'select iAlertClass'
SELECT @sql = @sql + ' ,sum(case iMatlFamily when '''+cast(iMatlFamily as varchar(10))+''' then dDQtyPre end) ['+vCode+']'
FROM T_MatlType WHERE iMatlFamily IN(SELECT DISTINCT iMatlFamily FROM T_MatlEnoughLev)
SELECT @sql = @sql + ' from T_MatlEnoughLev group by iAlertClass'
EXEC (@sql)
SET @sql = 'select iAlertClass'
SELECT @sql = @sql + ' ,sum(case iMatlFamily when '''+cast(iMatlFamily as varchar(10))+''' then dDQtyPre end) ['+vCode+']'
FROM T_MatlType WHERE iMatlFamily IN(SELECT DISTINCT iMatlFamily FROM T_MatlEnoughLev)
SELECT @sql = @sql + ' from T_MatlEnoughLev group by iAlertClass'
EXEC (@sql)
1 NULL NULL NULL NULL NULL
2 NULL NULL NULL NULL NULL
SET @sql = 'select iAlertClass'
SELECT @sql = @sql + ' ,sum(case id when '''+cast(id as varchar(10))+''' then dDQtyPre end) ['+vCode+']'
FROM T_MatlType WHERE id IN(SELECT DISTINCT iMatlFamily FROM T_MatlEnoughLev)
SELECT @sql = @sql + ' from T_MatlEnoughLev group by iAlertClass'
EXEC (@sql)
id int,
iMatlFamily int,
vCode varchar(50))
insert T_MatlType select 1,1,'FA'
union all select 2,2,'FB'create table T_MatlEnoughLev(
id int,
iAlertClass int,
iMatlFamily int,
dDQtyPre int)
insert T_MatlEnoughLev select 1,1,1,100
union all select 2,2,2,90
goDECLARE @sql varchar(8000)
SET @sql = 'select iAlertClass'
SELECT @sql = @sql + ' ,sum(case iMatlFamily when '''+cast(id as varchar(10))+''' then dDQtyPre end) ['+vCode+']'
FROM T_MatlType WHERE id IN(SELECT DISTINCT iMatlFamily FROM T_MatlEnoughLev)
SELECT @sql = @sql + ' from T_MatlEnoughLev group by iAlertClass'
EXEC (@sql)
godrop table T_MatlEnoughLev,T_MatlType
iAlertClass FA FB
----------- ----------- -----------
1 100 NULL
2 NULL 90
要拼接到@sql变量中的字段应该使用T_MatlType表的