select id=identity(int),* into #t from tb
declare @s varchar(8000)
set @s=''
select @s=@s+',['+yard+']=max(case id when '+cast(id as varchar)+' then qty else 0 end)'
from #t
exec('select color'+@s+' from #t group by color')
drop table #t
declare @s varchar(8000)
set @s=''
select @s=@s+',['+yard+']=max(case id when '+cast(id as varchar)+' then qty else 0 end)'
from #t
exec('select color'+@s+' from #t group by color')
drop table #t
select id=identity(int),* into #t from tb
declare @s varchar(8000)
set @s=''
select @s=@s+',['+yard+']=max(case id when '+cast(id as varchar)+' then qty else 0 end)'
from #t
exec('select color'+@s+' from #t group by color order by min(po)')
drop table #t
create table tb(po int,yard varchar(10),color varchar(10),qty int)
insert tb select 12,'x' ,'r',12
union all select 12,'xl','r',24
union all select 13,'m' ,'y',2
union all select 22,'xl','g',4
union all select 13,'x' ,'r',5
go--处理
select id=identity(int),* into #t from tb
declare @s varchar(8000)
set @s=''
select @s=@s+',['+yard+']=max(case id when '+cast(id as varchar)+' then qty else 0 end)'
from #t
exec('select color'+@s+' from #t group by color order by min(po)')
drop table #t
go--删除测试
drop table tb/*--测试结果color x xl m xl x
---------- ----------- ----------- ----------- ----------- -----------
r 12 24 0 0 5
y 0 0 2 0 0
g 0 0 0 4 0
--*/
select color, sum(case yard when x then qty else 0 end) as x,
sum(case yard when xl then qty else 0 end) as xl,
sum(case yard when m then qty else 0 end) as m,
sum(case yard when sl then qty else 0 end) as sl,
sum(case yard when s then qty else 0 end) as s,
from table
group by color如果没有错...怎么判断
12 x r NULL 12
13 x r NULL 5
这两个该分开放?用po吗?