select 国内采购 as 名称,
[模具]=sum(case when 汇率=1 then 数量 else 0 end),
[打印机]=sum(case when 汇率=1 then 数量 else 0 end),
[打印机]=sum(case when 汇率=1 then 数量 else 0 end)
from tablename group by 名称
union
select 进口数量 as 名称,
[模具]=sum(case when 汇率>1 then 数量 else 0 end),
[打印机]=sum(case when 汇率>1 then 数量 else 0 end),
[打印机]=sum(case when 汇率>1 then 数量 else 0 end)
from tablename group by 名称
[模具]=sum(case when 汇率=1 then 数量 else 0 end),
[打印机]=sum(case when 汇率=1 then 数量 else 0 end),
[打印机]=sum(case when 汇率=1 then 数量 else 0 end)
from tablename group by 名称
union
select 进口数量 as 名称,
[模具]=sum(case when 汇率>1 then 数量 else 0 end),
[打印机]=sum(case when 汇率>1 then 数量 else 0 end),
[打印机]=sum(case when 汇率>1 then 数量 else 0 end)
from tablename group by 名称
[模具]=sum(case when 名称='模具' then 数量 end),
[打印机]=sum(case when 汇率=1 then 数量 end),
[打印机]=sum(case when 汇率>1 then 数量 end)
from tablename group by case 名称 when 1 then '国内采购' else '进口数量' end
(
名稱 varchar(100), 匯率 numeric(14,2), 數量 int
)insert into tttt
select '模具' , 1 , 2 union all
select '打印機', 1.07 , 3 union all
select '打印機', 1 , 2
declare @sql1 nvarchar(4000)
set @sql1='select 匯率 as 名稱 '
select @sql1=@sql1+',sum(isnull(case when cast(名稱 as varchar)='''+cast(名稱 as varchar)+''' then 數量 else 0 end,0)) as '+cast(名稱 as varchar)
from (select distinct 名稱 from tttt) a
set @sql1=@sql1+' from (select 名稱,case when 匯率>1 then '+'''進口數量'''+' else '+'''國內采購'''+' end as 匯率,數量 from tttt) b group by 匯率 '
exec(@sql1)----------------------------
名稱 打印機 模具
國內采購 2 2
進口數量 3 0