create table cj --创建表cj ( ID Int IDENTITY (1,1) not null, -- Name Varchar(50), Subject Varchar(50), Result Int, primary key (ID) --定义ID为表cj的主键 );
Insert into cj Select '张三','语文',80 union all Select '张三','数学',90 union all Select '张三','物理',85 union all Select '李四','语文',85 union all Select '李四','物理',82 union all Select '李四','英语',90 union all Select '李四','政治',70 union all Select '王五','英语',90 ----原始数据 select * from cj ----标准实现 Declare @sql varchar(8000) Set @sql = 'Select Name as 姓名'--1 Select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result else 0 end) ['+Subject+']' from (select distinct Subject from cj) as cj --2 把所有唯一的科目的名称都列举出来 Select @sql = @sql+' from cj group by name'--3 Exec (@sql)
----一句实现 Select [Name] as 姓名, SUM(case Subject when '数学' then Result else 0 end) [数学], SUM(case Subject when '物理' then Result else 0 end) [物理], SUM(case Subject when '英语' then Result else 0 end) [英语], SUM(case Subject when '语文' then Result else 0 end) [语文], SUM(case Subject when '政治' then Result else 0 end) [政治] from cj group by [Name]
drop table cj
------解决方案-------------------- 这里
------解决方案-------------------- 顶
------解决方案-------------------- 用三个repeater
------解决方案--------------------
------解决方案--------------------
------解决方案--------------------
SQL code
-- 测试数据
declare @TempTable table(列1 varchar(10))
insert into @TempTable
select 'AAA'
union all select 'BBB'
union all select 'CCC'
-- 处理
declare @Value varchar(8000), @i int
select @Value='',@i=1
select @Value=@Value+',[列'+cast(@i as varchar)+']='''+列1+''''
,@i=@i+1
from @TempTable
set @Value=substring(@Value,2,8000)
exec('select '+@Value)
/*--测试结果
列1 列2 列3
---- ---- ---- ----
AAA BBB CCC
--*/
------解决方案-------------------- 这个简单,先做好个表格格式,循环输出指定值即可,
------解决方案-------------------- sql行转列后,绑定到gridview