declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case 仓号 when '+cast(仓号 as varchar)+' then 库存 else 0 end) ['+cast(仓号 as varchar)+'仓]'
from 库存表 group by 仓号
exec('select BOOKID'+@sql+' from 库存表 group by BOOKID order by BOOKID')
set @sql=''
select @sql=@sql+',sum(case 仓号 when '+cast(仓号 as varchar)+' then 库存 else 0 end) ['+cast(仓号 as varchar)+'仓]'
from 库存表 group by 仓号
exec('select BOOKID'+@sql+' from 库存表 group by BOOKID order by BOOKID')
set @sql='select bookid'
select @sql=@sql+',sum(case 仓号 when '+cast(仓号 as varchar)+' then 库存 else 0 end) ['+cast(仓号 as varchar)+'仓]'
from (select distinct 仓号 from 库存表)a
set @sql=@sql+' from 库存表 group by 仓号'
exec(@sql)
学习,如果仓号确定只有6个,可以直接写出SELECT bookID,
[1仓] =sum( case 仓号 when 1 then 库存 else 0 end),
[2仓] =sum( case 仓号 when 2 then 库存 else 0 end),
[3仓] =sum( case 仓号 when 3 then 库存 else 0 end),
[4仓] =sum( case 仓号 when 4 then 库存 else 0 end),
[5仓] =sum( case 仓号 when 5 then 库存 else 0 end),
[6仓] =sum( case 仓号 when 6 then 库存 else 0 end)
FROM 库存表 group by bookID order by bookID
insert into tk
select '19007728', 6, 12 union all
select '19007728' , 4, 40 union all
select '19007729' , 1, 0 union all
select '19007729' , 3, 22 union all
select '19007729' , 4, 110 union all
select '19007729' ,5, 0 union all
select '19007729' ,2, 52 union all
select '19007730' ,1, 21 union all
select '19007730' ,2, -10 union all
select '19007730' ,3, 0
----------------------------------------下面語句是一條完整的select bookid,[1仓]=(case when (select count(*) from tk where bookid=a.bookid and 仓号=1)>0 then ( select 库存 from tk where bookid=a.bookid and 仓号=1) else 0 end),
[2仓]=(case when (select count(*) from tk where bookid=a.bookid and 仓号=2)>0 then ( select 库存 from tk where bookid=a.bookid and 仓号=2) else 0 end),
[3仓]=(case when (select count(*) from tk where bookid=a.bookid and 仓号=3)>0 then ( select 库存 from tk where bookid=a.bookid and 仓号=3) else 0 end),
[4仓]=(case when (select count(*) from tk where bookid=a.bookid and 仓号=4)>0 then ( select 库存 from tk where bookid=a.bookid and 仓号=4) else 0 end),
[5仓]=(case when (select count(*) from tk where bookid=a.bookid and 仓号=5)>0 then ( select 库存 from tk where bookid=a.bookid and 仓号=5) else 0 end),
[6仓]=(case when (select count(*) from tk where bookid=a.bookid and 仓号=6)>0 then ( select 库存 from tk where bookid=a.bookid and 仓号=6) else 0 end)
from tk a group by bookid------------------結果為------------
BOOKID 1仓 2仓 3仓 4仓 5仓 6仓
19007728 0 0 0 40 0 12
19007729 0 52 22 110 0 0
19007730 21 -10 0 0 0 0--------------刪除測試---------
drop table Tk
insert into tk
select '19007728', 6, 12 union all
select '19007728' , 4, 40 union all
select '19007729' , 1, 0 union all
select '19007729' , 3, 22 union all
select '19007729' , 4, 110 union all
select '19007729' ,5, 0 union all
select '19007729' ,2, 52 union all
select '19007730' ,1, 21 union all
select '19007730' ,2, -10 union all
select '19007730' ,3, 0
----------------------------------------下面語句是一條完整的
-----------------------------------
declare @sql varchar(8000)
set @sql='select bookid'
select @sql=@sql+',sum(case 仓号 when '+cast(仓号 as varchar)+' then 库存 else 0 end) ['+cast(仓号 as varchar)+'仓]'
from (select distinct 仓号 from tk)a
set @sql=@sql+' from tk group by bookid'
exec(@sql)
BOOKID 1仓 2仓 3仓 4仓 5仓 6仓
19007728 0 0 0 40 0 12
19007729 0 52 22 110 0 0
19007730 21 -10 0 0 0 0