创建行转列表及插入数据
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
*/
sqlserver行列转换
标签：
