假设有张学生成绩表(tb_rowtocol)如下 Name Subject Result 张三 语文 73 张三 数学 83 张三 物理 93 李四 语文 74 李四 数学 84 李四 物理 94想变成 姓名 语文 数学 物理 张三 73 83 93 李四 74 84 94declare @sql varchar(4000) set @sql = 'select Name as ' + '姓名' select @sql = @sql + ' , sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']' from (select distinct Subject from rowtocol) as a set @sql = @sql + ' from rowtocol group by name' exec(@sql) 像你那样的结果,太不规则了,如果非要显示成那样,也不必非要查出的sql是那样,只要在程序中控制一下就可以了
因为在程序中写不好,所以才想在sql里面搞出来,呜呜
--以下为静态的写法.if object_id('pubs..tb') is not null drop table tb gocreate table tb ( 姓名 varchar(10), 科目 varchar(10), 分数 int )insert into tb(姓名,科目,分数) values('AAA', 'A', 5) insert into tb(姓名,科目,分数) values('AAA', 'B', 3) insert into tb(姓名,科目,分数) values('BBB', 'X', 4)select 姓名 , max(case when px = 1 then 科目 else null end) as 科目1, max(case when px = 1 then 分数 else null end) as 分数1, max(case when px = 2 then 科目 else null end) as 科目2, max(case when px = 2 then 分数 else null end) as 分数2 from ( select px=(select count(1) from tb where 姓名=a.姓名 and 分数<a.分数)+1 , 姓名,科目,分数 from tb a ) t group by 姓名 drop table tb姓名 科目1 分数1 科目2 分数2 ---------- ---------- ----------- ---------- ----------- AAA B 3 A 5 BBB X 4 NULL NULL(所影响的行数为 2 行)
--以下为动态的写法.if object_id('pubs..tb') is not null drop table tb gocreate table tb ( 姓名 varchar(10), 科目 varchar(10), 分数 int )insert into tb(姓名,科目,分数) values('AAA', 'A', 5) insert into tb(姓名,科目,分数) values('AAA', 'B', 3) insert into tb(姓名,科目,分数) values('BBB', 'X', 4)select px=(select count(1) from tb where 姓名=a.姓名 and 分数<a.分数)+1 , 姓名,科目,分数 into test from tb a declare @sql varchar(8000) set @sql = 'select 姓名' select @sql = @sql + ' , max(case cast(px as varchar) when ''' + cast(px as varchar) + ''' then 科目 else null end) [' + '科目' + cast(px as varchar) + ']' + ' , max(case cast(px as varchar) when ''' + cast(px as varchar) + ''' then 分数 else null end) [' + '分数' + cast(px as varchar) + ']' from (select distinct px from test) as a set @sql = @sql + ' from test group by 姓名' exec(@sql) drop table tb,test姓名 科目1 分数1 科目2 分数2 ---------- ---------- ----------- ---------- ----------- AAA B 3 A 5 BBB X 4 NULL NULL(所影响的行数为 2 行)
CREATE FUNCTION dbo.coltorow (@id varchar(100)) RETURNS varchar(1000) AS BEGIN declare @str varchar(1000) set @str='' select @str=@str+' '+科目+' ' +分数 from test where id=@id return @str ENDSELECT id, dbo.coltorow (id) AS 成绩 FROM test GROUP BY id只能连接,分列不行
对付科目数不定的方法 create table #tempt(姓名 varchar(10),科目 varchar(5),分数 int) insert into #tempt select 'AAA', 'A', 5 union all select 'AAA', 'B', 3 union all select 'BBB', 'X', 4 union all select 'BBB', 'B', 9 union all select 'BBB', 'C', 1 union all select 'BBB', 'F', 2 union all select 'AAA', 'W', 6 declare @i int,@j int,@sql varchar(8000) ---求最大科目数 select @i=max(num),@j=1,@sql='' from (select count(*) num from #tempt group by 姓名) a while @j<=@i begin set @sql=@sql+',(select 科目 from #tempt b'+convert(varchar,@j)+' where 姓名=a.姓名 and ' +'(select count(*) from #tempt where 姓名=a.姓名 and 科目<b'+convert(varchar,@j)+'.科目)=' +convert(varchar,@j-1)+') as 科目'+convert(varchar,@j) +',(select 分数 from #tempt c'+convert(varchar,@j)+' where 姓名=a.姓名 and ' +'(select count(*) from #tempt where 姓名=a.姓名 and 科目<c'+convert(varchar,@j)+'.科目)=' +convert(varchar,@j-1)+') as 分数'+convert(varchar,@j) set @j=@j+1 end set @sql='select 姓名'+@sql+' from #tempt a group by 姓名' exec(@sql) drop table #tempt姓名 科目1 分数1 科目2 分数2 科目3 分数3 科目4 分数4 ---------- ----- ----------- ----- ----------- ----- ----------- ----- ----------- AAA A 5 B 3 W 6 NULL NULL BBB B 9 C 1 F 2 X 4
amyangh() ( ) 信誉:100 Blog 2006-12-14 15:55:06 得分: 0
姓名 A B X
AAA 5 3 0
BBB 0 0 4
假设有张学生成绩表(tb_rowtocol)如下
Name Subject Result
张三 语文 73
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94想变成
姓名 语文 数学 物理
张三 73 83 93
李四 74 84 94declare @sql varchar(4000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'
from (select distinct Subject from rowtocol) as a
set @sql = @sql + ' from rowtocol group by name'
exec(@sql) 像你那样的结果,太不规则了,如果非要显示成那样,也不必非要查出的sql是那样,只要在程序中控制一下就可以了
drop table tb
gocreate table tb
(
姓名 varchar(10),
科目 varchar(10),
分数 int
)insert into tb(姓名,科目,分数) values('AAA', 'A', 5)
insert into tb(姓名,科目,分数) values('AAA', 'B', 3)
insert into tb(姓名,科目,分数) values('BBB', 'X', 4)select 姓名 ,
max(case when px = 1 then 科目 else null end) as 科目1,
max(case when px = 1 then 分数 else null end) as 分数1,
max(case when px = 2 then 科目 else null end) as 科目2,
max(case when px = 2 then 分数 else null end) as 分数2
from
(
select px=(select count(1) from tb where 姓名=a.姓名 and 分数<a.分数)+1 , 姓名,科目,分数 from tb a
) t
group by 姓名
drop table tb姓名 科目1 分数1 科目2 分数2
---------- ---------- ----------- ---------- -----------
AAA B 3 A 5
BBB X 4 NULL NULL(所影响的行数为 2 行)
drop table tb
gocreate table tb
(
姓名 varchar(10),
科目 varchar(10),
分数 int
)insert into tb(姓名,科目,分数) values('AAA', 'A', 5)
insert into tb(姓名,科目,分数) values('AAA', 'B', 3)
insert into tb(姓名,科目,分数) values('BBB', 'X', 4)select px=(select count(1) from tb where 姓名=a.姓名 and 分数<a.分数)+1 , 姓名,科目,分数 into test from tb a declare @sql varchar(8000)
set @sql = 'select 姓名'
select @sql = @sql + ' , max(case cast(px as varchar) when ''' + cast(px as varchar) + ''' then 科目 else null end) [' + '科目' + cast(px as varchar) + ']'
+ ' , max(case cast(px as varchar) when ''' + cast(px as varchar) + ''' then 分数 else null end) [' + '分数' + cast(px as varchar) + ']'
from (select distinct px from test) as a
set @sql = @sql + ' from test group by 姓名'
exec(@sql) drop table tb,test姓名 科目1 分数1 科目2 分数2
---------- ---------- ----------- ---------- -----------
AAA B 3 A 5
BBB X 4 NULL NULL(所影响的行数为 2 行)
RETURNS varchar(1000) AS
BEGIN declare @str varchar(1000)
set @str=''
select @str=@str+' '+科目+' ' +分数 from test where id=@id
return @str
ENDSELECT id, dbo.coltorow (id) AS 成绩
FROM test
GROUP BY id只能连接,分列不行
create table #tempt(姓名 varchar(10),科目 varchar(5),分数 int)
insert into #tempt select 'AAA', 'A', 5
union all select 'AAA', 'B', 3
union all select 'BBB', 'X', 4
union all select 'BBB', 'B', 9
union all select 'BBB', 'C', 1
union all select 'BBB', 'F', 2
union all select 'AAA', 'W', 6 declare @i int,@j int,@sql varchar(8000)
---求最大科目数
select @i=max(num),@j=1,@sql='' from (select count(*) num from #tempt group by 姓名) a
while @j<=@i
begin
set @sql=@sql+',(select 科目 from #tempt b'+convert(varchar,@j)+' where 姓名=a.姓名 and '
+'(select count(*) from #tempt where 姓名=a.姓名 and 科目<b'+convert(varchar,@j)+'.科目)='
+convert(varchar,@j-1)+') as 科目'+convert(varchar,@j)
+',(select 分数 from #tempt c'+convert(varchar,@j)+' where 姓名=a.姓名 and '
+'(select count(*) from #tempt where 姓名=a.姓名 and 科目<c'+convert(varchar,@j)+'.科目)='
+convert(varchar,@j-1)+') as 分数'+convert(varchar,@j)
set @j=@j+1
end
set @sql='select 姓名'+@sql+' from #tempt a group by 姓名'
exec(@sql)
drop table #tempt姓名 科目1 分数1 科目2 分数2 科目3 分数3 科目4 分数4
---------- ----- ----------- ----- ----------- ----- ----------- ----- -----------
AAA A 5 B 3 W 6 NULL NULL
BBB B 9 C 1 F 2 X 4
dawugui真是高人啊,我调通了,可是看不懂,呜呜!!!!!!!!!!!!!!!!!!!!!!!!!!!
你哪个地方没看懂?
AAA A 5 B 3
BBB X 4
这种格式的啊。
AAA A 5 B 3
BBB X 4
这种格式的啊。我是将同姓名按分数升序排的,你按降序排就是你要的结果px=(select count(1) from tb where 姓名=a.姓名 and 分数>a.分数)+1 --把 < 改为 > 即可.
where 科目 in
(select 科目 from test where 姓名 in(select 姓名 from test))