declare @sql varchar(8000) set @sql = 'select 系别 ' select @sql = @sql + ',sum(case 年龄 when '''+年龄+''' then 1 else 0 end) as '''+年龄+'''' from (select distinct 年龄 from test) as a select @sql = @SQL + ' from test group by 系别' exec(@sql) go 差不多是这样
if object_id('tbTest') is not null drop table tbTest GO create table tbTest (姓名 varchar(10),年龄 int, 系别 varchar(10)) insert tbTest select '张三',21,'英语' union all select '李四',22,'英语' union all select '王五',22,'生物' union all select '赵六',22,'生物' union all select '马七',21,'生物' union all select '冯八',23,'化学' ----查询 declare @sql varchar(8000) set @sql = 'select 系别' select @sql = @sql + ',[' + rtrim(年龄) + ']=sum(case 年龄 when ' + rtrim(年龄) + ' then 1 else 0 end)' from tbTest group by 年龄 EXEC(@sql + ' from tbTest group by 系别')drop table tbTest /*结果 系别 21 22 23 ---------- ----------- ----------- ----------- 化学 0 0 1 生物 1 2 0 英语 1 1 0 */
set @sql = 'select 系别 '
select @sql = @sql + ',sum(case 年龄 when '''+年龄+'''
then 1 else 0 end) as '''+年龄+''''
from (select distinct 年龄 from test) as a
select @sql = @SQL + ' from test group by 系别'
exec(@sql)
go
差不多是这样
drop table tbTest
GO
create table tbTest (姓名 varchar(10),年龄 int, 系别 varchar(10))
insert tbTest
select '张三',21,'英语' union all
select '李四',22,'英语' union all
select '王五',22,'生物' union all
select '赵六',22,'生物' union all
select '马七',21,'生物' union all
select '冯八',23,'化学' ----查询
declare @sql varchar(8000)
set @sql = 'select 系别'
select @sql = @sql + ',[' + rtrim(年龄) + ']=sum(case 年龄 when ' + rtrim(年龄) + ' then 1 else 0 end)'
from tbTest group by 年龄
EXEC(@sql + ' from tbTest group by 系别')drop table tbTest
/*结果
系别 21 22 23
---------- ----------- ----------- -----------
化学 0 0 1
生物 1 2 0
英语 1 1 0
*/