表如下:
A B
-------------
a 10
b 5
a 5
c 4
c 8
怎么用sql語句變成
a b c
---------
15 5 12
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+'['+rtrim(A)+']=sum(case A when '''+rtrim(A)+''' then B else 0 end),'
from table1 group by A
set @sql=left(@sql,len(@sql)-1)
print @sql
exec('select '+@sql+ ' from table1')
create table jzlist(儿童 varchar(10),疫苗 varchar(10),预种日期 datetime)
insert into jzlist
select '武绍阳','卡介苗','2001-02-03'
union all select '武绍阳','糖 丸','2001-03-25'
union all select '武绍阳','乙 肝','2001-04-06'
union all select '宁 远','卡介苗','2002-08-03'
union all select '宁 远','糖 丸','2002-11-25'
union all select '宁 远','乙 肝','2002-12-23'
union all select '李小颖','卡介苗','2003-01-03'
union all select '李小颖','糖 丸','2003-04-26'
union all select '李小颖','乙 肝','2003-05-01'
go
--查询处理:
declare @s varchar(8000)
set @s=''
select @s=@s+',['+疫苗+']=max(case when 疫苗='''+疫苗+''' then convert(char(10),预种日期,120) end)'
from (select distinct 疫苗 from jzlist) aa
exec('select 儿童' +@s+' from jzlist group by 儿童')
go