sqlserver行列转换

sqlserver⾏列转换sqlserver⾏转列
--创建⾏转列表及插⼊数据
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 else0end) 语⽂,
MAX(case course when'数学'then score else0end) 数学,
MAX(case course when'外语'then score else0end) 外语
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
*/
sqlserver列转⾏
--创建列转⾏表及插⼊数据
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
*/。