create table #t
(
StudentID int,
StudentName nvarchar(20),
courseName nvarchar(20),
[200901] int,
[200902] int,
[200903] int
)insert #t select 1,'a','chinese',80,85,90
insert #t select 1,'a','english',85,90,95原数据:
StudentID StudentName CourseName 200901 200902 200903 1 A Chinese 80 85 90 1 A English 85 90 95要转换成如下格式, 用SQL语句实现:YearMonth CourseName StudentID StudentName Score200901 Chinese 1 A 80200902 Chinese 1 A 85200903 Chinese 1 A 90
(
StudentID int,
StudentName nvarchar(20),
courseName nvarchar(20),
[200901] int,
[200902] int,
[200903] int
)insert #t select 1,'a','chinese',80,85,90
insert #t select 1,'a','english',85,90,95原数据:
StudentID StudentName CourseName 200901 200902 200903 1 A Chinese 80 85 90 1 A English 85 90 95要转换成如下格式, 用SQL语句实现:YearMonth CourseName StudentID StudentName Score200901 Chinese 1 A 80200902 Chinese 1 A 85200903 Chinese 1 A 90
union all
select '200902' as YearMonth,CourseName,StudentID,StudentName,[200902] from #t
union all
select '200903' as YearMonth,CourseName,StudentID,StudentName,[200903] from #t
(
StudentID int,
StudentName nvarchar(20),
courseName nvarchar(20),
[200901] int,
[200902] int,
[200903] int
)insert #t select 1,'a','chinese',80,85,90
insert #t select 1,'a','english',85,90,95select
*
from
(
select '200901' as YearMonth,CourseName,StudentID,StudentName,[200901] from #t
union all
select '200902' as YearMonth,CourseName,StudentID,StudentName,[200902] from #t
union all
select '200903' as YearMonth,CourseName,StudentID,StudentName,[200903] from #t
)t
where
CourseName<>'english'
drop table #t
/*YearMonth CourseName StudentID StudentName 200901
--------- -------------------- ----------- -------------------- -----------
200901 chinese 1 a 80
200902 chinese 1 a 85
200903 chinese 1 a 90(3 行受影响)
*/
(
StudentID int,
StudentName nvarchar(20),
courseName nvarchar(20),
[200901] int,
[200902] int,
[200903] int
)insert #t select 1,'a','chinese',80,85,90
insert #t select 1,'a','english',85,90,95select
*
from
(
select '200901' as YearMonth,CourseName,StudentID,StudentName,[200901] as score from #t
union all
select '200902' as YearMonth,CourseName,StudentID,StudentName,[200902] as score from #t
union all
select '200903' as YearMonth,CourseName,StudentID,StudentName,[200903] as socre from #t
)t
where
CourseName<>'english'
drop table #t
/*YearMonth CourseName StudentID StudentName score
--------- -------------------- ----------- -------------------- -----------
200901 chinese 1 a 80
200902 chinese 1 a 85
200903 chinese 1 a 90(3 行受影响)
*/
create table #t
(
StudentID int,
StudentName nvarchar(20),
courseName nvarchar(20),
[200901] int,
[200902] int,
[200903] int
)insert #t select 1,'a','chinese',80,85,90
insert #t select 1,'a','english',85,90,95select * from (
select '200901' yearm ,CourseName , StudentId , StudentName , [200901] Score from #t union all
select '200902' ,CourseName , StudentId , StudentName , [200902] Score from #t union all
select '200903' ,CourseName , StudentId , StudentName , [200903] Score from #t
) a order by CourseName , yearm
yearm CourseName StudentId StudentName Score
200901 chinese 1 a 80
200902 chinese 1 a 85
200903 chinese 1 a 90
200901 english 1 a 85
200902 english 1 a 90
200903 english 1 a 95(6 行受影响)
只有三列的情况,如果有多列的话就要用动态了
StudentName nvarchar(20),
courseName chinese wholesalers20),
[200901] int,
[200902] int,
[200903] int
)
create table #t
(
StudentID int,
StudentName nvarchar(20),
courseName nvarchar(20),
[200901] int,
[200902] int,
[200903] int
)insert #t select 1,'a','chinese',80,85,90
insert #t select 1,'a','english',85,90,95select * from (
select '200901' yearm ,CourseName , StudentId , StudentName , [200901] Score from #t union all
select '200902' ,CourseName , StudentId , StudentName , [200902] Score from #t union all
select '200903' ,CourseName , StudentId , StudentName , [200903] Score from #t
) a where CourseName='chinese' order by CourseName , yearm yearm CourseName StudentId StudentName Score
200901 chinese 1 a 80
200902 chinese 1 a 85
200903 chinese 1 a 90(3 行受影响)
(
StudentID int,
StudentName nvarchar(20),
courseName nvarchar(20),
[200901] int,
[200902] int,
[200903] int
)insert #t select 1,'a','chinese',80,85,90
insert #t select 1,'a','english',85,90,95select yearmonth = '200901' , coursename , studentid , studentname , [200901] from #t
union all
select yearmonth = '200902' , coursename , studentid , studentname , [200902] from #t
union all
select yearmonth = '200903' , coursename , studentid , studentname , [200903] from #t
order by yearmonth,coursename , studentid , studentnamedrop table #t/*
yearmonth coursename studentid studentname 200901
--------- -------------------- ----------- -------------------- -----------
200901 chinese 1 a 80
200901 english 1 a 85
200902 chinese 1 a 85
200902 english 1 a 90
200903 chinese 1 a 90
200903 english 1 a 95(所影响的行数为 6 行)
*/