SQL Server行列相互转换的方法详解

admin MsSql

2024-04-16 0 618

行转列

创建语句：

create table test1(
id int identity(1,1) not null,
name varchar(255) null,
course varchar(255) null,
score int null,
)

insert into test1(name, course, score) values (\’张三\’,\’语文\’, 80)
insert into test1(name, course, score) values (\’张三\’,\’数学\’, 52)
insert into test1(name, course, score) values (\’张三\’,\’英语\’, 150)
insert into test1(name, course, score) values (\’李四\’,\’语文\’, 44)
insert into test1(name, course, score) values (\’李四\’,\’数学\’, 111)
insert into test1(name, course, score) values (\’李四\’,\’英语\’, 110)
insert into test1(name, course, score) values (\’王五\’,\’语文\’, 140)
insert into test1(name, course, score) values (\’王五\’,\’数学\’, 80)
insert into test1(name, course, score) values (\’王五\’,\’英语\’, 92)
insert into test1(name, course, score) values (\’王五\’,\’物理\’, 77)
insert into test1(name, course, score) values (\’王五\’,\’化学\’, 65)

原始数据：

1、第一种方法：

— 使用case when then else ，这里也可以使用sum函数
select name,
max(case course when \’语文\’ then score else 0 end) as chinese,
max(case course when \’数学\’ then score else 0 end) as math,
max(case course when \’英语\’ then score else 0 end) as english,
max(case course when \’物理\’ then score else 0 end) as wuli,
max(case course when \’化学\’ then score else 0 end) as huaxue
from test1 group by name

第一种结果：

2、第二种方法：

— 使用pivot函数行转列
select name,max(t.语文)as chinese,max(t.数学)as math,max(t.英语)as english,max(t.物理)as wuli,max(t.化学)as huaxue
from test1 pivot(max(score) for course in(语文,数学,英语,物理,化学))t
group by name

第二种结果：

3、第三种方法：

有两种写法：

— 第一种写法，动态sql拼接，有多少行可以进行动态拼接sql，在列不确定的情况下可以使用
declare @sql_str varchar(8000); — 要执行的sql
declare @sql_col varchar(8000);
select @sql_col = isnull(@sql_col + \’,\’,\’\’) + quotename(course)
from test1 group by course;
print(@sql_col); — 打印数值列,不必需
set @sql_str = \’select * from (select name,course,score from test1)p pivot(sum(score) for course IN ( \’+ @sql_col +\’))as pvt order by pvt.name\’
print (@sql_str);–打印执行的sql
exec (@sql_str);– 执行查询

–第二种写法
declare @name varchar(100);
declare @max varchar(1000);
declare @sql nvarchar(4000);
select @name= stuff(
(select \’,\’+course+\’\’ from test1 group by course for xml path(\’\’)),1,1,\’\’);
select @max= stuff(
(select \’,max(\’+course+\’) as \’+course+\’\’ from test1 group by course for xml path(\’\’)),1,1,\’\’);
set @sql=\’select name,\’+@max+\’ from test1 pivot (max(score) for course in(\’+@name+\’)) css group by name\’;
exec(@sql);

第三种结果：

两种写法都是一样的结果

4、第四种方法：

— 使用distinct
select distinct a.name,
(select score from test1 b where a.name=b.name and b.course=\’语文\’ ) as \’chinese\’,
(select score from test1 b where a.name=b.name and b.course=\’数学\’ ) as \’math\’,
(select score from test1 b where a.name=b.name and b.course=\’英语\’ ) as \’english\’,
(select score from test1 b where a.name=b.name and b.course=\’物理\’ ) as \’wuli\’,
(select score from test1 b where a.name=b.name and b.course=\’化学\’ ) as \’huaxue\’
from test1 a

第四种结果：

列转行

创建语句：

create table test2(
id int identity(1,1) not null,
name varchar(255) null,
chinese int null,
math int null,
english int null,
wuli int null,
huaxue int null
)

insert into test2(name,chinese,math,english,wuli,huaxue) values (\’张三\’,110,120,85,null,null);
insert into test2(name,chinese,math,english,wuli,huaxue) values (\’李四\’,130,88,89,null,null);
insert into test2(name,chinese,math,english,wuli,huaxue) values (\’王五\’,93,124,87,98,67);

原始数据：

1、第一种方法：

union all与union的区别：

union all对结果集不会去除重复的结果，union会去除重复的结果

–第一种写法:
select row_number() over(order by id desc) as id,name,t.course,t.score from(
select id,name,course=\’语文\’,score=chinese from test2 union all
select id,name,course=\’数学\’,score=math from test2 union all
select id,name,course=\’英语\’,score=english from test2 union all
select id,name,course=\’物理\’,score=wuli from test2 union all
select id,name,course=\’化学\’,score=huaxue from test2
) t where score is not null order by id asc
— 下面可以不用执行，执行上面即可
,case t.course when \’语文\’ then 1 when \’数学\’ then 2 when \’英语\’ then 3 when \’物理\’ then 4 when \’化学\’ then 5 end

— 第二种写法：
select row_number() over(order by id desc) as id,name,t.course,t.score from(
select id,name,\’语文\’ as course, chinese as \’score\’ from test2 union
select id,name,\’数学\’ as course, math as \’score\’ from test2 union
select id,name,\’英语\’ as course, english as \’score\’ from test2 union
select id,name,\’物理\’ as course, wuli as \’score\’ from test2 union
select id,name,\’化学\’ as course, huaxue as \’score\’ from test2
) t where score is not null order by id asc
— 下面可以不用执行，执行上面即可
,case t.course when \’语文\’ then 1 when \’数学\’ then 2 when \’英语\’ then 3 when \’物理\’ then 4 when \’化学\’ then 5 end

第一种结果：

两种写法结果都是一样的

2、第二种方法：

–使用unpivot进行列转行
select row_number() over(order by id desc) as id,name,score,course from test2 unpivot( score for course in(chinese,math,english,wuli,huaxue))a

第二种结果：