SQL Server 中行转列列转行

最新推荐文章于 2024-10-18 18:14:36 发布

小DuDu

最新推荐文章于 2024-10-18 18:14:36 发布

阅读量652

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/weixin_36369910/article/details/53900363

本文介绍使用SQL进行数据转换的方法，包括利用CASE WHEN语句和PIVOT函数实现行转列，以及使用UNPIVOT操作实现列转行。这些技术对于处理复杂的数据格式非常有用。

行转列：

Create database Test
on primary (
   name='Test.mdf',
   fileName='D:\\project\Test.mdf',
   size=10mb,
   fileGrowth=15%
)
log on(
   name='Test.ndf',
   fileName='D:\\project\Test.ldf',
   size=3mb,
   fileGrowth=15%
)
go
use Test
go
create table Score(
   ID int primary key identity(1,1),
   name varchar(20) not null,
   course varchar(5),
   scores numeric(5,0)
)
go
drop table Score

insert into Score
Select '张三','语文',90.2union
Select '张三','数学',85.7union
Select '张三','英语',75union
Select '李四','语文',55union
Select '李四','英语',40union
Select '李四','数学',59union
select '王五','语文',60union
select '王五','数学',70union
select '王五','英语',30

1.通过Case where 条件1 then 结果1     多分支语句
select name as 姓名,SUM(case when course='语文' then scores else 0 end) as 语文成绩
,SUM(case when course='数学' then scores else 0 end) as 数学成绩
,SUM(case when course='英语' then scores else 0 end) as 英语成绩
from Score group by name

2.通过pivot

select * from Score
pivot(max(scores) for course in(语文,数学,英语)) b

列转行：

1.UNPIVOT

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
    Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO