表如下id fruit flowtype fcount
1 苹果 采购 3
2 苹果 采购 4
3 梨子 采购 4
4 苹果 卖出 4
5 梨子 卖出 2
6 梨子 采购 6
7 梨子 采购 3
8 苹果 卖出 8
9 梨子 卖出 2
10 梨子 采购 6想的到视图是
fruit 采购 卖出
苹果 26 16
梨子 19 4主要是要把flowType的值,转成列,最好能自动生成
1 苹果 采购 3
2 苹果 采购 4
3 梨子 采购 4
4 苹果 卖出 4
5 梨子 卖出 2
6 梨子 采购 6
7 梨子 采购 3
8 苹果 卖出 8
9 梨子 卖出 2
10 梨子 采购 6想的到视图是
fruit 采购 卖出
苹果 26 16
梨子 19 4主要是要把flowType的值,转成列,最好能自动生成
as
select fruit,
sum(case when flowtype='采购' then fcount else 0 end) as [采购],
sum(case when flowtype='卖出' then fcount else 0 end) as [卖出]
from tb
group by fruit
go
SUM(CASE FLOWTYPE WHEN '采购' THEN FCOUNT THEN 0 END) AS 采购,
SUM(CASE FLOWTYPE WHEN '卖出' THEN FCOUNT THEN 0 END) AS 卖出
FROM TB
select fruit,sum(a.fcount) as 采购,sum(b.fcount) as 卖出
from 表 as a,表 as b where a.fruit=b.fruit and a.flowtype='采购' and b.flowtype='卖出'试试
as
select fruit,
sum(case when flowtype='采购' then fcount else 0 end) as [采购],
sum(case when flowtype='卖出' then fcount else 0 end) as [卖出]
from tableName
group by fruit
go
declare @sql varchar(8000)
set @sql = 'create view v_fruit as select fruit '
select @sql = @sql + ' , max(case flowtype when ''' + flowtype + ''' then fcount else 0 end) [' + flowtype + ']'
from (select distinct flowtype from tableName) as a
set @sql = @sql + ' from tableName group by fruit'
exec(@sql)
set @sql = 'create view v_fruit as select fruit '
select @sql = @sql + ' , max(case flowtype when ''' + flowtype + ''' then fcount else 0 end) [' + flowtype + ']'
from (select distinct flowtype from tableName) as a
set @sql = @sql + ' from tableName group by fruit'
exec(@sql)
create table #aa (id int primary key identity(1,1),fruit varchar(50),flowtype varchar(50),fcount int)
insert into #aa values('苹果','采购',3)
insert into #aa values('苹果','采购',4)
insert into #aa values('梨子','采购',4)
insert into #aa values('苹果','卖出',4)
insert into #aa values('梨子','卖出',2)
insert into #aa values('梨子','采购',6)
insert into #aa values('梨子','采购',3)
select * from #aaselect fruit,
sum(case flowtype when '采购' then fcount else 0 END) AS 采购,
SUM(CASE FLOWTYPE WHEN '卖出' THEN FCOUNT else 0 END) AS 卖出
FROM #aa group by fruit结果是
fruit 采购 卖出
梨子 13 2
苹果 7 4