oracle - 使用 Sql Developer Oracle 的动态数据透视查询
问题描述
初始表学生
NAME School Class
John Hs English
Steve Hs Maths
Matthew Hs Science
Jim Hs History
需要输出:我需要查询自动从初始表中提取名称列数据并将其更改为输出中的列标题,并且由于名称会不断变化,我无法使用简单的数据透视查询对名称进行硬编码。我是枢轴查询的新手,所以我想询问是否有人可以帮助我。谢谢你。
School John Steve Matthew Jim
Hs English Maths Science History
这是我尝试过的: *注意(我正在尝试在 Oracle Sql Developer 中使用此查询来实现输出格式)
declare
sqlqry clob;
cols clob;
begin
select listagg('''' || NAME || ''' as "' || NAME || '"', ',') within group (order by NAME)
into cols
from (select distinct NAME from Students);
sqlqry :=
'
select * from(select NAME,SCHOOL,CLASS from Students)
pivot(MAX(CLASS) FOR NAME IN (' || cols || ')
)';
execute immediate sqlqry;
end;
解决方案
您走在正确的轨道上,但是当为至少一名学生定义一个以上的班级时,这种情况就会失败。内部的ROW_NUMBER()
分析功能解决了这个问题。因此,创建一个存储函数,包括SYS_REFCURSOR
:
CREATE OR REPLACE FUNCTION get_student_rs RETURN SYS_REFCURSOR IS
recordset SYS_REFCURSOR;
sqlqry VARCHAR2(32767);
cols VARCHAR2(32767);
BEGIN
SELECT LISTAGG(''''||name||''' AS "'||name||'"' ,',') WITHIN GROUP (ORDER BY 0)
INTO cols
FROM ( SELECT DISTINCT name FROM Students );
sqlqry :=
'SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY 0) AS rn,
s.*
FROM Students s
)
PIVOT (
MAX(class) FOR name IN ('||cols||')
)
ORDER BY rn';
OPEN recordset FOR sqlqry;
RETURN recordset;
END;
/
假设再插入一条记录;
Name School Class
---- ------ -------
Jim Hs History
然后调用
VAR rc REFCURSOR
EXEC :rc := get_student_rs;
PRINT rc
从SQL Developer的命令行查看结果集:
RN SCHOOL Jim John Matthew Steve
-- ------ ------- ------- ------- ------
1 Hs Maths English Science Maths
2 Hs History
推荐阅读
- google-cloud-storage - mb 命令需要一个指定存储桶的 URL
- python - 打印出所有以“.txt”结尾的标题(Python)
- amazon-web-services - 为什么 Amazon Cognito 不在其访问令牌中返回受众字段?
- python-2.7 - 无法将 python 字节转换为整数(int() 的无效文字,基数为 10:'\x16')
- python - 查看用户帖子时的Python Flask SQLite OperationalError
- kotlin - 从 Java 返回的 null 在 Kotlin 测试中的比较失败
- sql - 如何在 postgresql 中对 json 类型使用唯一约束
- python - 遍历多个目录的文件以提取数据
- spring - JUnit/Spring/MongoDB:单元测试因空值而失败
- php - 如何在 laravel 中设置两个 id 的关系?