--执行查询
declare @s varchar(8000)
set @s = ''
select @s = @s + ','+column_name+'=sum(case when item_code='''+item_code+''' then qty else 0 end)'
from item
set @s = 'select prod_name'+@s+' from trans_tab group by prod_name'
exec(@s)--输出结果
prod_name dz wj bz
--------- ---- ---- ----
a001 100 50 30
b001 55 11 80
declare @s varchar(8000)
set @s = ''
select @s = @s + ','+column_name+'=sum(case when item_code='''+item_code+''' then qty else 0 end)'
from item
set @s = 'select prod_name'+@s+' from trans_tab group by prod_name'
exec(@s)--输出结果
prod_name dz wj bz
--------- ---- ---- ----
a001 100 50 30
b001 55 11 80
create table item(item_code varchar(20),item_name varchar(20),column_name varchar(20))
insert into item select 'a','电子料','dz'
insert into item select 'b','五金料','wj'
insert into item select 'c','包装料','bz'
create table trans_tab(prod_name varchar(20),item_code varchar(20),qty numeric(18,0))
insert into trans_tab select 'a001','a',100
insert into trans_tab select 'a001','b',50
insert into trans_tab select 'a001','c',30
insert into trans_tab select 'b001','a',55
insert into trans_tab select 'b001','b',11
insert into trans_tab select 'b001','c',80--执行查询
declare @s varchar(8000)
set @s = ''
select @s = @s + ','+column_name+'=sum(case when item_code='''+item_code+''' then qty else 0 end)'
from item
set @s = 'select prod_name'+@s+' from trans_tab group by prod_name'
exec(@s)--输出结果
prod_name dz wj bz
--------- ---- ---- ----
a001 100 50 30
b001 55 11 80
Create Table item(item_code Varchar(20),item_name Nvarchar(20),column_name Varchar(20))
Create Table trans_tab(prod_name Varchar(20),item_code Varchar(20),qty Numeric(18,0))
--插入数据
Insert Into item Select 'a',N'电子料','dz'
Insert Into item Select 'b',N'五金料','wj'
Insert Into item Select 'c',N'包装料','bz'Insert Into trans_tab Select 'a001','a',100
Insert Into trans_tab Select 'a001','b',50
Insert Into trans_tab Select 'a001','c',30
Insert Into trans_tab Select 'b001','a',55
Insert Into trans_tab Select 'b001','b',11
Insert Into trans_tab Select 'b001','c',80
--测试
Declare @S Varchar(8000)
Set @S = ''
Select @S = @S + ','+column_name+'=SUM(Case When item_code='''+item_code+''' Then qty Else 0 End)' from item
Set @S = 'Select prod_name'+@S+' Into TEST from trans_tab Group By prod_name' --如果要更改生成的表名,将此处的TEST改为你要的表名
EXEC(@S)Select * from TEST
--删除测试环境
Drop Table item,trans_tab,TEST
--结果
/*
prod_name dz wj bz
a001 100 50 30
b001 55 11 80
*/
不可以用select into的方式去做.因为结果表的表名称及字段是已知的,其记录数量的字段名对应类别表中column_name字段
set @s = ''
select @s = @s + ',sum(case column_name when ''' + column_name + ''' then qty else null end) [' + column_name + ']'
from item,trans_tab
where item.item_code = trans_tab.item_code
group by item.column_nameselect @s = 'select prod_name ' +@s+' from item,trans_tab where item.item_code = trans_tab.item_code group by prod_name'exec(@s)
create table item(item_code varchar(20),item_name varchar(20),column_name varchar(20))
insert into item select 'a','电子料','dz'
insert into item select 'b','五金料','wj'
insert into item select 'c','包装料','bz'
create table trans_tab(prod_name varchar(20),item_code varchar(20),qty numeric(18,0))
insert into trans_tab select 'a001','a',100
insert into trans_tab select 'a001','b',50
insert into trans_tab select 'a001','c',30
insert into trans_tab select 'b001','a',55
insert into trans_tab select 'b001','b',11
insert into trans_tab select 'b001','c',80
create table t3(prod_name varchar(20),dz int,wj int,bz int)--执行查询
declare @s varchar(8000),@v varchar(8000)
set @s = ''
set @v = ''
select
@s = @s + ','+column_name+'=sum(case when item_code='''+item_code+''' then qty else 0 end)',
@v = @v + ','+column_name
from item
set @s = 'insert into t3(prod_name'+@v+') select prod_name'+@s+' from trans_tab group by prod_name'
print @s
exec(@s)
select * from t3--输出结果
prod_name dz wj bz
--------- ---- ---- ----
a001 100 50 30
b001 55 11 80
to:paoluo
不可以用select into的方式去做.因为结果表的表名称及字段是已知的,其记录数量的字段名对应类别表中column_name字段
---------------------------
哦,我以为你不知道的,那改用Insert就可以啊。
if exists 则update ,否则就插入