日期:2014-05-18  浏览次数:20602 次

怎样把多行的东西汇总到一行?
产品名称 数量 交货期
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 dt
select @dtSTR=stuff(@dtSTR,1,1,'')
--print @dtSTR
exec( 'select id,' +@dtStr + ' 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 @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,行列转换