sql 行列转换

最新推荐文章于 2020-04-18 13:08:02 发布

jsm8523

最新推荐文章于 2020-04-18 13:08:02 发布

阅读量216

点赞数

分类专栏： sql

本文链接：https://blog.csdn.net/jsm8523/article/details/78614318

版权

sql 专栏收录该内容

15 篇文章

订阅专栏

-- 行转列

CREATE TABLE [ss_text]
(
[UserName] NVARCHAR(20), --学生姓名
[Subject] NVARCHAR(30), --科目
[Score] FLOAT, --成绩
)

INSERT INTO [ss_text] SELECT '张三', '语文', 80
INSERT INTO [ss_text] SELECT '张三', '数学', 90
INSERT INTO [ss_text] SELECT '张三', '英语', 70
INSERT INTO [ss_text] SELECT '张三', '生物', 85
INSERT INTO [ss_text] SELECT '李四', '语文', 80
INSERT INTO [ss_text] SELECT '李四', '数学', 92
INSERT INTO [ss_text] SELECT '李四', '英语', 76
INSERT INTO [ss_text] SELECT '李四', '生物', 88
INSERT INTO [ss_text] SELECT '码农', '语文', 60
INSERT INTO [ss_text] SELECT '码农', '数学', 82
INSERT INTO [ss_text] SELECT '码农', '英语', 96
INSERT INTO [ss_text] SELECT '码农', '生物', 78

SELECT * FROM [ss_text] /*数据源*/
PIVOT
(
sum(Score/*行转列后列的值*/) FOR
Subject/*需要行转列的列*/ IN ([语文],[数学],[英语],[生物]/*列的值*/)
) AS T

-------------------------------------------------------------------------------------------------------

SELECT * FROM [ss_text]
PIVOT (SUM (Score) FOR Subject IN (语文,数学,英语,生物)) AS T

-- 列转行

CREATE TABLE [dbo].[sgt](
[rlrq] [varchar](20) NULL,
[mr] [decimal](20, 4) NULL,
[mc] [decimal](20, 4) NULL,
[lx] [varchar](10) NULL
) ON [PRIMARY]

insert into sgt values('2011-11-22',1,11,'a')
insert into sgt values('2011-11-22',2,22,'b')

select rlrq,
max(case lx when 'a' then mr else null end) as a,
max(case lx when 'b' then mr else null end) as b
from sgt where rlrq='2011-11-20' group by rlrq
union
select rlrq,
max(case lx when 'a' then mc else null end) as a,
max(case lx when 'b' then mc else null end) as b
from sgt where rlrq='2011-11-20' group by rlrq