本文介紹了Oracle 將行轉(zhuǎn)為列的處理方法，對(duì)大家解決問題具有一定的參考價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)吧！
問題描述
限時(shí)送ChatGPT賬號(hào)..
我有以下表格:
TABLE1
------
id name
1 n1
2 n2
TABLE2
------
id tipo valor
1 t1 v1
1 t2 v2
2 t1 v1
2 t2 v5
2 t3 v3
我正在努力:
id name t1 t2 t3
1 n1 v1 v2 -
2 n2 v1 v5 v3
有可能嗎?我一直在尋找示例，但仍然沒有弄清楚.
Is it possible? I have been looking examples but still I don't get nothing clear.
謝謝！
推薦答案
一種選擇是使用 PIVOT Clause
One option would be using PIVOT Clause
SELECT *
FROM (SELECT t1.*, t2.tipo, t2.valor
FROM table1 t1
JOIN table2 t2
ON t2.id = t1.id)
PIVOT
(
MAX(valor) FOR tipo IN ( 't1' AS "t1", 't2' AS "t2", 't3' AS "t3" )
)
但這是靜態(tài)的，例如.需要更新 tipo 列的不同值.為了使其動(dòng)態(tài)化，您可以創(chuàng)建一個(gè)函數(shù)
but this is static, eg. needs to be updated as different values for tipo column is added. In order to make it dynamic, you can create a function
CREATE OR REPLACE FUNCTION Get_Pivoted_Cols RETURN SYS_REFCURSOR IS
v_recordset SYS_REFCURSOR;
v_sql VARCHAR2(32767);
v_cols VARCHAR2(32767);
BEGIN
SELECT LISTAGG( ''''||tipo||''' AS "'||tipo||'"' , ',' )
WITHIN GROUP ( ORDER BY tipo )
INTO v_cols
FROM ( SELECT DISTINCT tipo FROM table2 );
v_sql := 'SELECT *
FROM (SELECT t1.*, t2.tipo, t2.valor
FROM table1 t1
JOIN table2 t2
ON t2.id = t1.id)
PIVOT
(
MAX(valor) FOR tipo IN ( '|| v_cols ||' )
)';
OPEN v_recordset FOR v_sql;
RETURN v_recordset;
END;
/
然后使用調(diào)用
VAR rc REFCURSOR
EXEC :rc := Get_Pivoted_Cols;
PRINT rc
來自 SQL 開發(fā)人員的控制臺(tái).
from SQL developer's console.
演示
這篇關(guān)于Oracle 將行轉(zhuǎn)為列的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！
【網(wǎng)站聲明】本站部分內(nèi)容來源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問題，如果有圖片或者內(nèi)容侵犯了您的權(quán)益，請(qǐng)聯(lián)系我們刪除處理，感謝您的支持！