学号 课程号 成绩 课程号 成绩 课程号 成绩
001101 101 80 206 76 102 78
001101 101 80 102 78 206 76
001101 102 78 206 76 101 80
001101 102 78 101 80 206 76
001101 206 76 102 78 101 80
001101 206 76 101 80 102 78
001103 101 62 206 81 102 70
001103 101 62 102 70 206 81
001103 102 70 206 81 101 62
001103 102 70 101 62 206 81
001103 206 81 102 70 101 62
001103 206 81 101 62 102 70
001104 101 90 206 65 102 84
001104 101 90 102 84 206 65
001104 102 84 206 65 101 90
001104 102 84 101 90 206 65
001104 206 65 102 84 101 90
001104 206 65 101 90 102 84
001106 101 65 206 80 102 71
001106 101 65 102 71 206 80
001106 102 71 206 80 101 65
001106 102 71 101 65 206 80
001106 206 80 102 71 101 65
001106 206 80 101 65 102 71
001107 101 78 206 68 102 80
001107 101 78 102 80 206 68
001107 102 80 206 68 101 78
001107 102 80 101 78 206 68
001107 206 68 102 80 101 78
001107 206 68 101 78 102 80
001108 101 85 206 87 102 64
001108 101 85 102 64 206 87
001108 102 64 206 87 101 85
001108 102 64 101 85 206 87
001108 206 87 102 64 101 85
001108 206 87 101 85 102 64
001110 101 95 206 89 102 90
001110 101 95 102 90 206 89
001110 102 90 206 89 101 95
001110 102 90 101 95 206 89
001110 206 89 102 90 101 95
001110 206 89 101 95 102 90
001111 101 91 206 76 102 70
001111 101 91 102 70 206 76
001111 102 70 206 76 101 91
001111 102 70 101 91 206 76
001111 206 76 102 70 101 91
001111 206 76 101 91 102 70
001113 101 63 206 60 102 79
001113 101 63 102 79 206 60
001113 102 79 206 60 101 63
001113 102 79 101 63 206 60
001113 206 60 102 79 101 63
001113 206 60 101 63 102 79如何合并 成
学号 课程号 成绩 课程号 成绩 课程号 成绩
001101 101 80 102 78 206 76
001103 101 62 102 70 206 81
001104 101 90 102 84 206 65
001106 101 65 102 71 206 80
001107 101 78 102 80 206 68
001108 101 85 102 64 206 87
001110 101 95 102 90 206 89
001111 101 91 102 70 206 76
001113 101 63 102 79 206 60
001101 101 80 206 76 102 78
001101 101 80 102 78 206 76
001101 102 78 206 76 101 80
001101 102 78 101 80 206 76
001101 206 76 102 78 101 80
001101 206 76 101 80 102 78
001103 101 62 206 81 102 70
001103 101 62 102 70 206 81
001103 102 70 206 81 101 62
001103 102 70 101 62 206 81
001103 206 81 102 70 101 62
001103 206 81 101 62 102 70
001104 101 90 206 65 102 84
001104 101 90 102 84 206 65
001104 102 84 206 65 101 90
001104 102 84 101 90 206 65
001104 206 65 102 84 101 90
001104 206 65 101 90 102 84
001106 101 65 206 80 102 71
001106 101 65 102 71 206 80
001106 102 71 206 80 101 65
001106 102 71 101 65 206 80
001106 206 80 102 71 101 65
001106 206 80 101 65 102 71
001107 101 78 206 68 102 80
001107 101 78 102 80 206 68
001107 102 80 206 68 101 78
001107 102 80 101 78 206 68
001107 206 68 102 80 101 78
001107 206 68 101 78 102 80
001108 101 85 206 87 102 64
001108 101 85 102 64 206 87
001108 102 64 206 87 101 85
001108 102 64 101 85 206 87
001108 206 87 102 64 101 85
001108 206 87 101 85 102 64
001110 101 95 206 89 102 90
001110 101 95 102 90 206 89
001110 102 90 206 89 101 95
001110 102 90 101 95 206 89
001110 206 89 102 90 101 95
001110 206 89 101 95 102 90
001111 101 91 206 76 102 70
001111 101 91 102 70 206 76
001111 102 70 206 76 101 91
001111 102 70 101 91 206 76
001111 206 76 102 70 101 91
001111 206 76 101 91 102 70
001113 101 63 206 60 102 79
001113 101 63 102 79 206 60
001113 102 79 206 60 101 63
001113 102 79 101 63 206 60
001113 206 60 102 79 101 63
001113 206 60 101 63 102 79如何合并 成
学号 课程号 成绩 课程号 成绩 课程号 成绩
001101 101 80 102 78 206 76
001103 101 62 102 70 206 81
001104 101 90 102 84 206 65
001106 101 65 102 71 206 80
001107 101 78 102 80 206 68
001108 101 85 102 64 206 87
001110 101 95 102 90 206 89
001111 101 91 102 70 206 76
001113 101 63 102 79 206 60
解决方案 »
- 诚招软件代理
- 求教:查询出座机<8位或手机<11位的数据,只要其中一个条件满足则数据是完整的
- 请问有没有什么工具可以方便的得到SQl Server的数据字典?
- SQL中关于类型为NTEXT的数据项如何输入特殊字符?
- 无法启动sql server
- 哪位大哥可以给段C中嵌入式SQL的例子给我小弟?
- sql2000 1053错误 服务没有及时响应启动或控制请求
- 说是from附近有语法错误
- 请教各位高手:我有20000条数据,如何实现1000条一次的数据检索。例如:
- 如何把一个存储过程返回的结果集存入到一个临时表?
- 一个高难度的表的纵横变换处理
- 谁的机器装有sql 2005 麻烦帮忙把两个sql 2005 数据库转成sql 2000的。
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html?33238(总结帖子)
http://topic.csdn.net/u/20090912/14/25d2e1b2-f352-4713-8618-d3433ba27bef.html?99104(经典帖子)
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([学号] varchar(6),[课程号] int,[成绩] int)
insert [tb]
select '001101',101,80 union all
select '001101',102,78 union all
select '001101',206,76 union all
select '001103',101,62 union all
select '001103',102,70 union all
select '001103',206,81 union all
select '001104',101,90 union all
select '001104',102,84 union all
select '001104',206,65 union all
select '001102',102,78 union all
select '001102',206,78 union all
select '001106',101,65 union all
select '001106',102,71 union all
select '001106',206,80 union all
select '001107',101,78 union all
select '001107',102,80 union all
select '001107',206,68 union all
select '001108',101,85 union all
select '001108',102,64 union all
select '001108',206,87 union all
select '001109',102,83 union all
select '001109',206,70 union all
select '001110',101,95 union all
select '001110',102,90 union all
select '001110',206,89 union all
select '001111',101,91 union all
select '001111',102,70 union all
select '001111',206,76 union all
select '001113',101,63 union all
select '001113',102,79 union all
select '001113',206,60 union all
select '001201',101,80 union all
select '001202',101,65 union all
select '001203',101,87 union all
select '001204',101,91 union all
select '001210',101,76 union all
select '001216',101,81 union all
select '001218',101,70 union all
select '001220',101,82 union all
select '001221',101,76 union all
select '001241',101,90
---查询---
declare @sql varchar(max)
select
@sql=isnull(@sql+',','')
+'max(case when px='+ltrim(px)+' then 课程号 else '''' end) as [课程号'+ltrim(px)+'],'
+'max(case when px='+ltrim(px)+' then 成绩 else 0 end) as [成绩'+ltrim(px)+']'
from(
select distinct
px= row_number() over (partition by 学号 order by 课程号)
from tb
) t
set @sql='select 学号,'+@sql
+' from
(
select *,
px= row_number() over (partition by 学号 order by 课程号)
from tb
) t group by 学号'
--print @sql
exec(@sql)/**
学号 课程号1 成绩1 课程号2 成绩2 课程号3 成绩3
------ ----------- ----------- ----------- ----------- ----------- -----------
001101 101 80 102 78 206 76
001102 102 78 206 78 0 0
001103 101 62 102 70 206 81
001104 101 90 102 84 206 65
001106 101 65 102 71 206 80
001107 101 78 102 80 206 68
001108 101 85 102 64 206 87
001109 102 83 206 70 0 0
001110 101 95 102 90 206 89
001111 101 91 102 70 206 76
001113 101 63 102 79 206 60
001201 101 80 0 0 0 0
001202 101 65 0 0 0 0
001203 101 87 0 0 0 0
001204 101 91 0 0 0 0
001210 101 76 0 0 0 0
001216 101 81 0 0 0 0
001218 101 70 0 0 0 0
001220 101 82 0 0 0 0
001221 101 76 0 0 0 0
001241 101 90 0 0 0 0(21 行受影响)**/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([学号] varchar(6),[课程号] int,[成绩] int)
insert [tb]
select '001101',101,80 union all
select '001101',102,78 union all
select '001101',206,76 union all
select '001103',101,62 union all
select '001103',102,70 union all
select '001103',206,81 union all
select '001104',101,90 union all
select '001104',102,84 union all
select '001104',206,65 union all
select '001102',102,78 union all
select '001102',206,78 union all
select '001106',101,65 union all
select '001106',102,71 union all
select '001106',206,80 union all
select '001107',101,78 union all
select '001107',102,80 union all
select '001107',206,68 union all
select '001108',101,85 union all
select '001108',102,64 union all
select '001108',206,87 union all
select '001109',102,83 union all
select '001109',206,70 union all
select '001110',101,95 union all
select '001110',102,90 union all
select '001110',206,89 union all
select '001111',101,91 union all
select '001111',102,70 union all
select '001111',206,76 union all
select '001113',101,63 union all
select '001113',102,79 union all
select '001113',206,60 union all
select '001201',101,80 union all
select '001202',101,65 union all
select '001203',101,87 union all
select '001204',101,91 union all
select '001210',101,76 union all
select '001216',101,81 union all
select '001218',101,70 union all
select '001220',101,82 union all
select '001221',101,76--------------------------------查询开始------------------------------select a.[学号],a.[课程号],a.[成绩],b.[课程号],b.[成绩],c.[课程号],c.[成绩] from
(select * from [tb] where [课程号]=101) a
join
(select * from [tb] where [课程号]=102) b on a.[学号]=b.[学号]
join
(select * from [tb] where [课程号]=206) c on a.[学号]=c.[学号]
/*
学号 课程号 成绩 课程号 成绩 课程号 成绩
------ ----------- ----------- ----------- ----------- ----------- -----------
001101 101 80 102 78 206 76
001103 101 62 102 70 206 81
001104 101 90 102 84 206 65
001106 101 65 102 71 206 80
001107 101 78 102 80 206 68
001108 101 85 102 64 206 87
001110 101 95 102 90 206 89
001111 101 91 102 70 206 76
001113 101 63 102 79 206 60(9 行受影响)
*/
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
-- QQ :584738179
-- Development Tool :Microsoft Visual C++ 6.0 C Language
-- FUNCTION :CONVERT DATA TO T-SQL
---------------------------------------------------------------
-- Microsoft SQL Server 2005
-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]
---------------------------------------------------------------
---------------------------------------------------------------use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
学号 char(7),
课程号1 int,
成绩1 int,
课程号2 int,
成绩2 int,
课程号3 int,
成绩3 int
)
go
--插入测试数据
insert into tb select '001101',101,80,206,76,102,78
union all select '001101',101,80,102,78,206,76
union all select '001101',102,78,206,76,101,80
union all select '001101',102,78,101,80,206,76
union all select '001101',206,76,102,78,101,80
union all select '001101',206,76,101,80,102,78
union all select '001103',101,62,206,81,102,70
union all select '001103',101,62,102,70,206,81
union all select '001103',102,70,206,81,101,62
union all select '001103',102,70,101,62,206,81
union all select '001103',206,81,102,70,101,62
union all select '001103',206,81,101,62,102,70
union all select '001104',101,90,206,65,102,84
union all select '001104',101,90,102,84,206,65
union all select '001104',102,84,206,65,101,90
union all select '001104',102,84,101,90,206,65
union all select '001104',206,65,102,84,101,90
union all select '001104',206,65,101,90,102,84
union all select '001106',101,65,206,80,102,71
union all select '001106',101,65,102,71,206,80
union all select '001106',102,71,206,80,101,65
union all select '001106',102,71,101,65,206,80
union all select '001106',206,80,102,71,101,65
union all select '001106',206,80,101,65,102,71
union all select '001107',101,78,206,68,102,80
union all select '001107',101,78,102,80,206,68
union all select '001107',102,80,206,68,101,78
union all select '001107',102,80,101,78,206,68
union all select '001107',206,68,102,80,101,78
union all select '001107',206,68,101,78,102,80
union all select '001108',101,85,206,87,102,64
union all select '001108',101,85,102,64,206,87
union all select '001108',102,64,206,87,101,85
union all select '001108',102,64,101,85,206,87
union all select '001108',206,87,102,64,101,85
union all select '001108',206,87,101,85,102,64
union all select '001110',101,95,206,89,102,90
union all select '001110',101,95,102,90,206,89
union all select '001110',102,90,206,89,101,95
union all select '001110',102,90,101,95,206,89
union all select '001110',206,89,102,90,101,95
union all select '001110',206,89,101,95,102,90
union all select '001111',101,91,206,76,102,70
union all select '001111',101,91,102,70,206,76
union all select '001111',102,70,206,76,101,91
union all select '001111',102,70,101,91,206,76
union all select '001111',206,76,102,70,101,91
union all select '001111',206,76,101,91,102,70
union all select '001113',101,63,206,60,102,79
union all select '001113',101,63,102,79,206,60
union all select '001113',102,79,206,60,101,63
union all select '001113',102,79,101,63,206,60
union all select '001113',206,60,102,79,101,63
go
--代码实现select 学号,
sum(case when id%3=1 then 课程号1 end) 课程号,
sum(case when id%3=1 then 成绩1 end) 成绩,
sum(case when id%3=2 then 课程号1 end) 课程号,
sum(case when id%3=2 then 成绩1 end) 成绩,
sum(case when id%3=0 then 课程号1 end) 课程号,
sum(case when id%3=0 then 成绩1 end) 成绩
from(select id=row_number()over(order by getdate()),* from(select distinct 学号,课程号1,成绩1 from tb)m)t
group by 学号
/*测试结果学号 课程号 成绩 课程号 成绩 课程号 成绩
----------------------------------------------
001101 101 80 102 78 206 76
001103 101 62 102 70 206 81
001104 101 90 102 84 206 65
001106 101 65 102 71 206 80
001107 101 78 102 80 206 68
001108 101 85 102 64 206 87
001110 101 95 102 90 206 89
001111 101 91 102 70 206 76
001113 101 63 102 79 206 60(9 行受影响)
*/
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
-- QQ :584738179
-- Development Tool :Microsoft Visual C++ 6.0 C Language
-- FUNCTION :CONVERT DATA TO T-SQL
---------------------------------------------------------------
-- Microsoft SQL Server 2005
-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]
---------------------------------------------------------------
---------------------------------------------------------------use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
学号 char(7),
课程号 int,
成绩 int
)
go
--插入测试数据
insert into tb select '001101',101,80
union all select '001101',102,78
union all select '001101',206,76
union all select '001103',101,62
union all select '001103',102,70
union all select '001103',206,81
union all select '001104',101,90
union all select '001104',102,84
union all select '001104',206,65
union all select '001102',102,78
union all select '001102',206,78
union all select '001106',101,65
union all select '001106',102,71
union all select '001106',206,80
union all select '001107',101,78
union all select '001107',102,80
union all select '001107',206,68
union all select '001108',101,85
union all select '001108',102,64
union all select '001108',206,87
union all select '001109',102,83
union all select '001109',206,70
union all select '001110',101,95
union all select '001110',102,90
union all select '001110',206,89
union all select '001111',101,91
union all select '001111',102,70
union all select '001111',206,76
union all select '001113',101,63
union all select '001113',102,79
union all select '001113',206,60
union all select '001201',101,80
union all select '001202',101,65
union all select '001203',101,87
union all select '001204',101,91
union all select '001210',101,76
union all select '001216',101,81
union all select '001218',101,70
union all select '001220',101,82
union all select '001221',101,76
go
--代码实现
declare @temp table(id int identity(1,1),学号 char(7),课程号 int,成绩 int)
insert into @temp select * from tb where 学号 in(select 学号 from tb group by 学号 having count(*)=3)
select 学号,sum(case when id%3=1 then 课程号 end) 课程号,
sum(case when id%3=1 then 成绩 end) 成绩,
sum(case when id%3=2 then 课程号 end) 课程号,
sum(case when id%3=2 then 成绩 end) 成绩,
sum(case when id%3=0 then 课程号 end) 课程号,
sum(case when id%3=0 then 成绩 end) 成绩
from @temp group by 学号
/*测试结果学号 课程号 成绩 课程号 成绩 课程号 成绩
----------------------------------------------
001101 101 80 102 78 206 76
001103 101 62 102 70 206 81
001104 101 90 102 84 206 65
001106 101 65 102 71 206 80
001107 101 78 102 80 206 68
001108 101 85 102 64 206 87
001110 101 95 102 90 206 89
001111 101 91 102 70 206 76
001113 101 63 102 79 206 60(9 行受影响)
*/