SQL Server基础之行数据转换为列数据基础之行数据转换为列数据
准备工作准备工作
创建表创建表
use [test1] go
create table [dbo].[student](
[id] [int] identity(1,1) not null,
[name] [nvarchar](50) null,
[project] [nvarchar](50) null,
[score] [int] null,
constraint [pk_student] primary key clustered
(
[id] asc
)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on)
on [primary] ) on [primary] go
插入数据
insert into test1.dbo.student(name,project,score)
values('张三','android','60'),
('张三','ios','70'),
('张三','html5','55'),
('张三','.net','100'),
('李四','android','60'),
('李四','ios','75'),
('李四','html5','90'),
('李四','.net','100');
使用使用Case When和聚合函数进行行专列和聚合函数进行行专列
语法语法
select column_name,
<aggregation function>(<case when expression>)
from database.schema.table
group by column_name
语法解析语法解析
column_name
数据列列名
aggregation function
聚合函数，常见的有：sum,max,min,avg,count等。
case when expression
case when表达式
示例示例
select name,
max(case project when 'android' then score end) as '安卓',
max(case project when 'ios' then score end) as '苹果',
max(case project when 'html5' then score end) as 'html5',
max(case project when '.net' then score end) as '.net'
from [test1].[dbo].[student] group by name
示例结果示例结果
转换前
资源评论
