在 Oracle 中使用动态列进行透视 [英] Pivot with Dynamic Columns in Oracle
问题描述
我是新的 oracle 数据库我有一个如下表
I am new oracle database I have a table like below
ID Passengers Age Eligible
123456 Ben 65 Yes
123456 Mary 58 Yes
123458 Stephanie 37 Yes
123458 Aaron 32 Yes
123458 Caroline 18 No
我想得到动态列名称为 Age1、Age2、Age3 等的结果.如下
I want to get the result with dynamic column name as Age1,Age2,Age3 etc.. as below
ID Age1 Age2 Age3
123456 65 58 NULL
123458 37 32 18
它可以通过 SQL Server
中的 STUFF 和 Dynamic Pivot 实现,但我不知道如何在 Oracle
It can be achieved with STUFF and Dynamic Pivot in SQL Server
but I am not aware how to achieve this in Oracle
谁能指导我如何在Oracle
推荐答案
SYS_REFCURSOR
在存储函数中(例如使用 PL/SQL 而不是直接使用 SQL)可能用于获取动态生成的结果集(例如Dynamic Pivot).在这种情况下,会生成一个用于条件聚合的字符串:
SYS_REFCURSOR
within a stored function(eg. PL/SQL used instead of using SQL directly) might be used in order to get dynamically generated result set(eg. Dynamic Pivot). In this case, a string for Conditional Aggregation is generated :
CREATE OR REPLACE FUNCTION get_passengers_rs RETURN SYS_REFCURSOR IS
v_recordset SYS_REFCURSOR;
v_sql VARCHAR2(32767);
v_str VARCHAR2(32767);
BEGIN
SELECT LISTAGG('MAX(CASE WHEN rn = '||lvl||' THEN age||''(''||passengers||'')'' END)
AS "Age'||lvl||'"' ,',') WITHIN GROUP (ORDER BY 0)
INTO v_str
FROM ( SELECT level AS lvl
FROM dual
CONNECT BY level <= (SELECT MAX(COUNT(*)) FROM t GROUP BY ID ) ) t;
v_sql :=
'SELECT ID, '|| v_str ||'
FROM
(
SELECT t.*,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY 0) AS rn
FROM t
)
GROUP BY ID';
OPEN v_recordset FOR v_sql;
RETURN v_recordset;
END;
为了更好的区分每个数据,我还添加了乘客姓名.
I also added the names of passengers in order to distinguish each data well.
然后运行下面的代码:
VAR rc REFCURSOR
EXEC :rc := get_passengers_rs;
PRINT rc
从 SQL Developer 的命令行查看预期结果集.
from SQL Developer's Command Line in order to see the expected result set.
以上代码为当前存在的数据生成此 SQL 字符串(v_sql)
Above code generates this SQL string(v_sql) for the currently existing data
SELECT ID, MAX(CASE WHEN rn = 1 THEN age||'('||passengers||')' END) AS "Age1",
MAX(CASE WHEN rn = 2 THEN age||'('||passengers||')' END) AS "Age2",
MAX(CASE WHEN rn = 3 THEN age||'('||passengers||')' END) AS "Age3"
FROM
(
SELECT t.*,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY 0) AS rn
FROM t
)
GROUP BY ID
产生的结果
ID Age1 Age2 Age3
123456 58(Marie) 65(Ben)
123458 32(Aaron) 18(Caroline) 37(Stephanie)
作为结果集.
这篇关于在 Oracle 中使用动态列进行透视的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!