--执行查询
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
解决方案 »
- sql 日期提醒问题
- 使用过SQL Server 2005 解决方案的来
- 求一条动态SQL语句
- 想知道记录所处在表中的位置!!大家新年快乐
- 发现MSSQLServer2000关于视图的一个大问题,不知道是Bug?
- 论坛置顶贴的sql问题
- 服务器windows2003上的Sql2000_sq4突然不能访问,重启电脑后正常,过几个小时后又不能访问了.
- 有谁知道怎么给数据库改名呀
- 请救,varchar(20)的最大长度只能为20,只在0-20只间吗?这样定义后不能超过20个字符了吗
- 请问,如何将某个日期字段中填写不规范的数据格式化为标准的日期格式?
- 求一高难度实战型SQL语句,涉及父子关系。在线急等!!!50分!!高手进!!
- Distinct的排序问题,求救!!!!!!!!
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 ,否则就插入