--创建行转列表及插入数据
create table tb_RowConvertToColumn
(
username nvarchar(100) null,
course nvarchar(100) null,
score numeric(10,2) null
)
insert into tb_RowConvertToColumn(username,course,score) values('张三','语文',82)
insert into tb_RowConvertToColumn(username,course,score) values('张三','数学',85)
insert into tb_RowConvertToColumn(username,course,score) values('张三','外语',90)
insert into tb_RowConvertToColumn(username,course,score) values('李四','语文',86)
insert into tb_RowConvertToColumn(username,course,score) values('李四','数学',82)
insert into tb_RowConvertToColumn(username,course,score) values('李四','外语',92)
insert into tb_RowConvertToColumn(username,course,score) values('王五','语文',82)
insert into tb_RowConvertToColumn(username,course,score) values('王五','数学',94)
insert into tb_RowConvertToColumn(username,course,score) values('王五','外语',82)
--1.静态sql行转列，该sql指定了转换的列头
select username 姓名,
MAX(case course when '语文' then score else 0 end) 语文,
MAX(case course when '数学' then score else 0 end) 数学,
MAX(case course when '外语' then score else 0 end) 外语
from tb_RowConvertToColumn
group by username
order by username
/*
姓名 语文 数学 外语
李四 86.00 82.00 92.00
王五 82.00 94.00 82.00
张三 82.00 85.00 90.00
*/
--2.静态sql行转列，该sql指定了转换的列头，该语句必须sqlserver2005及以上版本才能使用
select username 姓名,语文,数学,外语 from tb_RowConvertToColumn pivot(max(score) for course in(语文,数学,外语)) a
/*
姓名 语文 数学 外语
李四 86.00 82.00 92.00
王五 82.00 94.00 82.00
张三 82.00 85.00 90.00
*/
select * from tb_RowConvertToColumn pivot(max(score)for course in (语文,数学,外语)) a
/*
username 语文 数学 外语
李四 86.00 82.00 92.00
王五 82.00 94.00 82.00
张三 82.00 85.00 90.00
*/
--3.动态sql行转列，自动生成转换的列
declare @sql nvarchar(2000)
select distinct course into #tb_group from tb_RowConvertToColumn order by course desc--表头及排序
select @sql=ISNULL(@sql+',','')+'MAX(case course when '''+course+''' then score else 0 end) ['+course+']'
from #tb_group
set @sql='select username 姓名,'+@sql
+' from tb_RowConvertToColumn a'
+' group by username'
exec(@sql)
drop table #tb_group
/*
姓名 语文 外语 数学
李四 86.00 92.00 82.00
王五 82.00 82.00 94.00
张三 82.00 90.00 85.00
*/
--4.动态sql行转列，自动生成转换的列，该语句必须sqlserver2005及以上版本才能使用
declare @sql nvarchar(2000)
select @sql=ISNULL(@sql+',','')+course
from tb_RowConvertToColumn
group by course
set @sql='select * from tb_RowConvertToColumn pivot (max(score) for course in ('+@sql+')) a'
exec(@sql)
/*
username 数学 外语 语文
李四 82.00 92.00 86.00
王五 94.00 82.00 82.00
张三 85.00 90.00 82.00
*/
--创建列转行表及插入数据
create table tb_ColumnConvertToRow
(
[姓名] nvarchar(100) null,
[语文] nvarchar(100) null,
[数学] nvarchar(100) null,
[外语] nvarchar(100) null
)
insert into tb_ColumnConvertToRow(姓名,语文,数学,外语) values('李四',82,92,86)
insert into tb_ColumnConvertToRow(姓名,语文,数学,外语) values('王五',94,82,82)
insert into tb_ColumnConvertToRow(姓名,语文,数学,外语) values('张三',85,90,82)
--1.静态sql列转行,当列头较少时使用
select * from
(
select 姓名,课程='语文',分数=语文 from tb_ColumnConvertToRow
union all
select 姓名,课程='数学',分数=数学 from tb_ColumnConvertToRow
union all
select 姓名,课程='外语',分数=外语 from tb_ColumnConvertToRow
) a
/*
姓名 课程 分数
李四 语文 82
王五 语文 94
张三 语文 85
李四 数学 92
王五 数学 82
张三 数学 90
李四 外语 86
王五 外语 82
张三 外语 82
*/
--2.静态sql列转行,当列头较少时使用，该语句必须sqlserver2005及以上版本才能使用
select 姓名,课程,分数 from tb_ColumnConvertToRow unpivot(分数 for 课程 in (语文,数学,外语)) a
/*
姓名 课程 分数
李四 语文 82
李四 数学 92
李四 外语 86
王五 语文 94
王五 数学 82
王五 外语 82
张三 语文 85
张三 数学 90
张三 外语 82
*/
