日期:2014-05-18 浏览次数:20602 次
create table #t(id varchar,amount int,dt varchar(10)) insert #t select 'A', 10 ,'2007-1-1' union all select 'B', 5 ,'2007-1-1' union all select 'A', 3 ,'2007-5-6' union all select 'A', 7 ,'2007-5-7' union all select 'c', 10 ,'2008-1-1' declare @dtSTR varchar(1000) select @dtSTR=isnull(@dtSTR,'') + ',[' + dt + ']=MAX(CASE WHEN dt=''' + dt + ''' THEN AMOUNT ELSE 0 END)' from #t group by dt order by dt select @dtSTR=stuff(@dtSTR,1,1,'') --print @dtSTR exec( 'select id,' +@dtStr + ' from #t group by id') drop table #t
------解决方案--------------------
create table b(产品名称 varchar(10), 数量 int, 交货期 datetime) insert into b select 'A',10,'2007-1-1' insert into b select 'B' , 5 , '2007-1-1' insert into b select 'A' , 3 , '2007-5-6' insert into b select 'A', 7 , '2007-5-7' declare @sql varchar(1000) set @sql='select 产品名称' select @sql=@sql+',[交货期]=max(case 交货期 when '''+convert(varchar(10),交货期,120)+''' then 数量 else 0 end)' from b group by 交货期 set @sql=@sql+' from b group by 产品名称' exec(@sql)
------解决方案--------------------
insert .......
union all
select * ............
------解决方案--------------------
用静态或动态sql,行列转换