--测试环境
create table WuZi_Table(物资编码 varchar(10),颜色编号 varchar(10),数量 int)
insert into WuZi_Table select '1111',1,10
union all select '2222',2,20
union all select '1111',1,20
union all select '3333',3,5
union all select '1111',3,10
create table YanSe_Table(颜色编号 int,颜色名称 varchar(10))
insert into YanSe_Table select 1,'红色'
union all select 2,'兰色'
union all select 3,'绿色'--动态SQL
declare @s varchar(2000)
set @s='select A.物资编码'
select @s=@s+',['+B.颜色名称+']=sum(case when A.颜色编号='+A.颜色编号+' then 数量 else 0 end ) '
from WuZi_Table A inner join YanSe_Table B
on A.颜色编号=B.颜色编号
group by A.颜色编号,B.颜色名称
set @s=@s+' from WuZi_Table A inner join YanSe_Table B '
set @s=@s+'on A.颜色编号=B.颜色编号'
set @s=@s+' group by A.物资编码'
exec(@s)--结果物资编码 红色 兰色 绿色
---------- ----------- ----------- -----------
1111 30 0 10
2222 0 20 0
3333 0 0 5--删除测试环境
drop table WuZi_Table,YanSe_Table
create table WuZi_Table(物资编码 varchar(10),颜色编号 varchar(10),数量 int)
insert into WuZi_Table select '1111',1,10
union all select '2222',2,20
union all select '1111',1,20
union all select '3333',3,5
union all select '1111',3,10
create table YanSe_Table(颜色编号 int,颜色名称 varchar(10))
insert into YanSe_Table select 1,'红色'
union all select 2,'兰色'
union all select 3,'绿色'--动态SQL
declare @s varchar(2000)
set @s='select A.物资编码'
select @s=@s+',['+B.颜色名称+']=sum(case when A.颜色编号='+A.颜色编号+' then 数量 else 0 end ) '
from WuZi_Table A inner join YanSe_Table B
on A.颜色编号=B.颜色编号
group by A.颜色编号,B.颜色名称
set @s=@s+' from WuZi_Table A inner join YanSe_Table B '
set @s=@s+'on A.颜色编号=B.颜色编号'
set @s=@s+' group by A.物资编码'
exec(@s)--结果物资编码 红色 兰色 绿色
---------- ----------- ----------- -----------
1111 30 0 10
2222 0 20 0
3333 0 0 5--删除测试环境
drop table WuZi_Table,YanSe_Table
declare @sql varchar(8000)
set @sql='select id 物资编码,'
select @sql=@sql+'sum(case Color when '''+Color+''' then WuZi_Table else 0 end) as '''+Color+''','
from (select distinct Color from YanSe_Table) as a begin
select @sql = left(@sql,len(@sql)-1) + ' into ##temp from WuZi_Table where group by id' end
--试试看
select * from ##temp