Sql 行转列

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(字段名)

 

Sql 行转列

标签：image   ast   money   sql   tab   logs   sel   实现   ase