准备工作
创建表
use [test1]gocreate 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示例结果
转换前
转换后
使用PIVOT进行行专列
PIVOT通过将表达式中一列中的唯一值转换为输出中的多个列来旋转表值表达式。并PIVOT在最终输出中需要的任何剩余列值上运行聚合，PIVOT提供比一系列复杂的SELECT…CASE语句指定的语法更为简单和可读的语法，PIVOT执行聚合并将可能的多行合并到输出中的单个行中。
语法
select <non-pivoted column>,
[first pivoted column] as <column name>,
[second pivoted column] as <column name>,
...
[last pivoted column] as <column name>
from
(<select query that produces the data>)
as <alias for the source query>
pivot
(
<aggregation function>(<column being aggregated>)
