Sql 行转列

1.创建一个表，并准备下数据源

 1 Create table  Student 
 2 (
 3  ID int primary key Identity(1,1) not null,
 4  StuName nvarchar(50),
 5  CourseName nvarchar(50),
 6  Score int,
 7  CreateTime dateTime 
 8 
 9 )
10 
11 insert into Student values ('张三','语文',81,GETDATE())
12 insert into Student values ('张三','数学',82,GETDATE())
13 insert into Student values ('张三','英语',83,GETDATE())
14 insert into Student values ('李四','语文',86,GETDATE())
15 insert into Student values ('李四','数学',87,GETDATE())
16 insert into Student values ('李四','英语',88,GETDATE())

2. 用 Case When 实现

1 select stuName,
2  max(case CourseName when '语文' then Score end) as '语文',
3 max(case CourseName when '数学' then Score end) as '数学',
4 max(case CourseName when '英语' then Score end) as '英语'
5 from Student  group by StuName

3.用微软提供的PIVOT函数实现

select stuName,[语文], [数学], [英语] from 
(
 select stuName,Score,CourseName from Student
) as p
PIVOT ( sum(Score) for CourseName IN ([语文], [数学], [英语]))t

4.记录一下一个给数字添加千分字符的函数

SELECT  '111111111' as old, CONVERT(VARCHAR(15),CAST(CONVERT(DECIMAL(15,2),LTRIM(111111111)) AS MONEY),1)  dollar --LIRIM(字段名)