产品名称 数量 交货期
A 10 2007-1-1
B 5 2007-1-1
A 3 2007-5-6
A 7 2007-5-7
变成如下格式：
产品名称 交货期
2007-1-1 2007-5-6 2007-5-7
A 10 3 7
B 5
用Select 语句怎么写？
------解决方案--------------------
- SQL code
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 dtselect @dtSTR=stuff(@dtSTR,1,1,'')--print @dtSTRexec( 'select id,' [email protected] + ' from #t group by id')drop table #t
------解决方案--------------------
- SQL code
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 @[email protected]+',[交货期]=max(case 交货期 when '''+convert(varchar(10),交货期,120)+''' then 数量 else 0 end)' from b group by 交货期 set @[email protected]+' from b group by 产品名称'exec(@sql)
------解决方案--------------------
insert .......
union all
select * ............
------解决方案--------------------
用静态或动态sql，行列转换