declare @sql varchar(8000)
set @sql = 'sfbatchno as 客退单号
,fcheckqty as 检验数量
,fitemname as 成品型号 '
select @sql = @sql + ' , max(case Fqcitemname when ''' + Fqcitemname + ''' then fitemsamplebadqty else 0 end) [' + Fqcitemname + ']'
from (select distinct Fqcitemname from #temp1) as t
set @sql = @sql + ' from #temp1 group by fbatchno,fcheckqty,fitemname,Fqcitemname'
exec(@sql)这个查询结果出来的是一张二维表,能否转到指定的一张表里面去,比如#temp
set @sql = 'sfbatchno as 客退单号
,fcheckqty as 检验数量
,fitemname as 成品型号 '
select @sql = @sql + ' , max(case Fqcitemname when ''' + Fqcitemname + ''' then fitemsamplebadqty else 0 end) [' + Fqcitemname + ']'
from (select distinct Fqcitemname from #temp1) as t
set @sql = @sql + ' from #temp1 group by fbatchno,fcheckqty,fitemname,Fqcitemname'
exec(@sql)这个查询结果出来的是一张二维表,能否转到指定的一张表里面去,比如#temp
select * into #T from Tb where .....
set @sql = 'sfbatchno as 客退单号
,fcheckqty as 检验数量
,fitemname as 成品型号 '
select @sql = @sql + ' , max(case Fqcitemname when ''' + Fqcitemname + ''' then fitemsamplebadqty else 0 end) [' + Fqcitemname + ']'
from (select distinct Fqcitemname from #temp1) as t
set @sql = @sql + ' into tmp from #temp1 group by fbatchno,fcheckqty,fitemname,Fqcitemname'
exec(@sql)
set @sql = 'sfbatchno as 客退单号
,fcheckqty as 检验数量
,fitemname as 成品型号 '
select @sql = @sql + ' , max(case Fqcitemname when ''' + Fqcitemname + ''' then fitemsamplebadqty else 0 end) [' + Fqcitemname + ']'
from (select distinct Fqcitemname from #temp1) as t
set @sql = @sql + ' into ##temp from #temp1 group by fbatchno,fcheckqty,fitemname,Fqcitemname'
exec(@sql) select * from ##temp
GO
CREATE TABLE TB(ID INT ,[NAME] VARCHAR(10))
INSERT TB SELECT 1,'A' UNION ALL
SELECT 3,'B' UNION ALL
SELECT 5,'C' UNION ALL
SELECT 10,'D'
EXEC('SELECT * INTO TEMP FROM TB')
SELECT * FROM TEMP
DROP TABLE TEMP
/*ID NAME
----------- ----------
1 A
3 B
5 C
10 D(4 行受影响)*/
declare @sql varchar(8000)
set @sql = 'sfbatchno as 客退单号
,fcheckqty as 检验数量
,fitemname as 成品型号 '
select @sql = @sql + ' , max(case Fqcitemname when ''' + Fqcitemname + ''' then fitemsamplebadqty else 0 end) [' + Fqcitemname + ']'
from (select distinct Fqcitemname from #temp1) as t
set @sql = @sql + ' into #tmp from #temp1 group by fbatchno,fcheckqty,fitemname,Fqcitemname'
exec('select ' +@sql)
最后insert #temp exec(@sql)
create table #temp
(
....
)--再通过exec插入临时表insert into #temp exec(@sql)