sql 纵表横表转换_sql将查询结果横转纵-CSDN博客

本文链接：https://blog.csdn.net/ZGM55555/article/details/125821318

本文介绍了在MySQL和Oracle数据库中如何进行纵表与横表的转换。纵表转横表使用了CASE语句结合SUM函数进行聚合，而横表转纵表则通过UNION ALL或UNPIVOT操作实现。这些转换技巧对于数据处理和分析至关重要。

摘要由CSDN通过智能技术生成

sql 纵表横表转换

纵表横表

纵表：
原纵表
横表：
输出横表

MySQL转换

纵表转横表

-- 	 纵表转横表
 SELECT `name`,
	sum(case `subject` when '语文' then achievement else 0 end) as 语文,
	sum(case `subject` when '数学' then achievement else 0 end) as 数学
 FROM student_achievement    
 GROUP BY `name`;

横表转纵表

--  横表转纵表
 select `name`,'math' as 课程,math as 成绩 from student_achevement2
 union all
 select `name`,'verbal' as 课程,verbal as 成绩 from student_achevement2
 order by name,课程 desc

Oracle转换

纵表转横表

-- 用decode实现,竖表转横表
SELECT NAME,
MAX(DECODE(TIMENAME, 'BEGIN_TIME', TIME)) BEGIN_TIME,
MAX(DECODE(TIMENAME, 'END_TIME', TIME))
END_TIME
FROM ZGM_TSET_LINE
GROUP BY NAME

-- 用case when实现,竖表转横表
SELECT NAME,
MAX(CASE TIMENAME WHEN 'BEGIN_TIME' THEN TIME ELSE NULL END) BEGIN_TIME,
MAX(CASE TIMENAME WHEN 'END_TIME' THEN TIME ELSE NULL END) END_TIME
FROM ZGM_TSET_LINE
GROUP BY NAME

-- 用pivot() 函数实现,竖表转横表
SELECT NAME ,MAX(BEGIN_TIME) AS BEGIN_TIME ,MAX(END_TIME) AS END_TIME 
FROM (
    SELECT * 
    FROM ZGM_TSET_LINE
pivot(
	max(TIME)for TIMENAME in ('BEGIN_TIME' as BEGIN_TIME,'END_TIME' as END_TIME)
)) 
GROUP BY NAME ORDER BY NAME

横表转纵表

-- 用union all实现,横表转竖表
select * from
(
select NAME,'BEGIN_TIME' AS TIMENAME,BEGIN_TIME AS TIME from ZGM_TEST_VERTACIL
union all
select NAME,'END_TIME' AS TIMENAME,END_TIME AS TIME from ZGM_TEST_VERTACIL
) t
order by t.NAME, case t.TIMENAME when 'BEGIN_TIME' then 1 when 'END_TIME' then 2 end

-- 用unpivot()函数实现,横表转竖表
select NAME,TIME_NAME,TIMEE from ZGM_TEST_VERTACIL
unpivot
(
	TIMEE for TIME_NAME in(BEGIN_TIME,END_TIME)
)ORDER BY NAME,TIME_NAME