set nocount on --加上这句,你就可以把这个当成一个sql,用recordset对象打开
declare @s varchar(8000)
set @s=''
select @s=@s+',['+s_zhlbmc+']=sum(case s_zhlbmc when '''+s_zhlbmc+''' then 1 else 0 end)'
from kl_zh group by s_zhlbmc
exec('
select s_dwjc'+@s+'
from kl_zh
group by s_dwjc')
select s_dwjc,sum(case s_zhlbmc when '基本存款账户' then 1 else 0 end) as 基本存款账户' sum( case s_zhlbmc when '二' then 1 else 0 end) as 二,sum(case s_zhlbmc when '一' then 1 else 0 end) as 一
from kl_zh
group by s_dwjc
select s_dwjc , case s_zhlbmc when '基本存款' then (select count(s_zhlbmc) from kl_zh where s_zhlbmc= '基本存款' ) end as 基本存款,
case s_zhlbmc when '二' then (select count(s_zhlbmc) from kl_zh where s_zhlbmc= '二') end as 二,
case s_zhlbmc when '一' then (select count(s_zhlbmc) from kl_zh where s_zhlbmc= '一') end as 一
from kl_zh
Create table test (name char(10),km char(10),cj int)
go
insert test values('张三','语文',80)
insert test values('张三','数学',86)
insert test values('张三','英语',75)
insert test values('李四','语文',78)
insert test values('李四','数学',85)
insert test values('李四','英语',78)
想变成
姓名 语文 数学 英语
张三 80 86 75
李四 78 85 78
declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']'
from (select distinct km from test) as a
select @sql = @sql+' from test group by name'
exec(@sql)
--如果 s_zhlbmc 不固定
declare @s varchar(8000)
set @s=''
select @s=@s+',['+s_zhlbmc+']=sum(case s_zhlbmc when '''+s_zhlbmc+''' then 1 else 0 end)'
from kl_zh group by s_zhlbmc
exec('
select s_dwjc'+@s+'
from kl_zh
group by s_dwjc')
select s_dwjc
,[基本存款账户]=sum(case s_zhlbmc when '基本存款账户' then 1 else 0 end)
,[账户二]=sum(case s_zhlbmc when '账户二' then 1 else 0 end)
,[账户一]=sum(case s_zhlbmc when '账户一' then 1 else 0 end)
from kl_zh
group by s_dwjc