行列互换的例子:create table test0000(类别 nvarchar(20), 数量 int, 反款 money,代办商名 nvarchar(20))
insert into test0000 select '充值卡100',80,1.75,'通大' union all
select '充值卡50',10,1.0,'通大' union all
select '充值卡100',120,1.75,'伟达' union all
select '充值卡50',32,1.0,'伟达'
declare @s varchar(8000)
set @s = ''
select @s = @s + ','+rtrim(类别)+'=sum(case when 类别 = '''+rtrim(类别)+''' and 代办商名=a.代办商名 then 数量 else 0 end),
反款 = sum(case when 类别 = '''+rtrim(类别)+''' and 代办商名=a.代办商名 then 反款 else 0 end),
金额 = sum(case when 类别 = '''+rtrim(类别)+''' and 代办商名=a.代办商名 then 反款*数量 else 0 end)'
from
test0000
group by
类别
print @s
set @s = 'select 代办商名' + @s + ' from test0000 a group by 代办商名'
print @s
exec(@s)
drop table test0000
insert into test0000 select '充值卡100',80,1.75,'通大' union all
select '充值卡50',10,1.0,'通大' union all
select '充值卡100',120,1.75,'伟达' union all
select '充值卡50',32,1.0,'伟达'
declare @s varchar(8000)
set @s = ''
select @s = @s + ','+rtrim(类别)+'=sum(case when 类别 = '''+rtrim(类别)+''' and 代办商名=a.代办商名 then 数量 else 0 end),
反款 = sum(case when 类别 = '''+rtrim(类别)+''' and 代办商名=a.代办商名 then 反款 else 0 end),
金额 = sum(case when 类别 = '''+rtrim(类别)+''' and 代办商名=a.代办商名 then 反款*数量 else 0 end)'
from
test0000
group by
类别
print @s
set @s = 'select 代办商名' + @s + ' from test0000 a group by 代办商名'
print @s
exec(@s)
drop table test0000
select t1.货品ID,t1.零售价,t2.进货价 from
(select 货品ID,价格 as 零售价 from table where 价格类型 = '零售价') t1,
(select 货品ID,价格 as 进货价 from table where 价格类型 = '进货价') t2
where t1.货品ID = t2.货品ID
max(decode(价格类型,'零售价',价格,-99999)) 零售价,
max(decode(价格类型,'进货价',价格,-99999)) 进货价
from tbname group by 货品ID;
select 货品ID,max(零售价),max(进货价) from
(select 货品ID, case 价格类型 when '零售价' then 价格 else -9999 end as 零售价,
case 价格类型 when '进货价' then 价格 else -9999 end as 进货价 from table)
group by 货品ID