Oracle行列操作--合并行與按字段拆分
1、在實際工作中遇到根據某一字段將多行合并成一行的情況，我們下面以選修課的例子進行說明：
-- create table create table XXK ( id NUMBER, rymc NVARCHAR2(50), xxkmc NVARCHAR2(50) ) ---insert test data insert into XXK values(1,'小明','編程'); insert into XXK values(2,'小明','繪畫'); insert into XXK values(3,'小明','音樂'); insert into XXK values(4,'小紅','繪畫'); insert into XXK values(5,'小紅','音樂');
如果我們要實現將所有的人員顯示為一行，選修的不同可成通過逗號連接呢（下圖效果）？
這個可以使用Oracle的LISTAGG函數，其具體語法如下
LISTAGG(column, delimiter) WITHIN GROUP (ORDER BY column)
其中：
column為需要合并的列的名稱
delimiter為分割符號
那么實現的語句我們可以寫成：
SELECT T.RYMC, LISTAGG(TO_CHAR(T.XXKMC), ',') WITHIN GROUP(ORDER BY XXKMC) AS XXKLIST FROM XXK t GROUP BY T.RYMC
特別注意：
我在具體使用過程中，有時候往往寫的語句沒有問題，就是不返回結果或者返回結果有亂碼等情況，這是因為字段的數據類型導致的，進行一下轉換即可，就如上面的SQL，其通過TO_CHAR(T.XXKMC)對選修課名稱進行了類型轉換。
2、如果我要實現相反的效果呢？先制作測試數據。
-- create table create table XXK2 ( id NUMBER, rymc NVARCHAR2(50), xxkmc NVARCHAR2(50) ) ---insert test data insert into XXK2 values(1,'小明','編程,繪畫,音樂'); insert into XXK2 values(2,'小紅','繪畫,音樂');
這里可以使用regexp_substr函數來實現：
regexp_substr(str, pattern [, position [, occurrence [, match_param]]])
其中：
str是需要進行處理的字符串列
pattern是正則表達式
position其實位置，從第幾個字符開始匹配
occurrence獲取第幾個分割出來的組
match_param模式，i不區分大小寫，c為區分大小寫，默認是c
具體語句如下：
SELECT T.RYMC, REGEXP_SUBSTR(T.XXKMC, '[^,]+', 1, Level) AS XXK_CF FROM XXK2 T CONNECT BY Level<= REGEXP_COUNT (T.XXKMC, ',' ) +1 and t.RYMC = prior t.RYMC and prior dbms_random.value is not null
注意：
在本例中，level并不是固定不變的，要根據實際選修的課程數來確定，其中REGEXP_COUNT就是來做這個工作的。
需要通過人員進行限定同時處理空的情況t.RYMC = prior t.RYMC and prior dbms_random.value is not null，否則會出現錯亂的情況，如下所示：
posted on 2023-08-23 18:35 jingkunliu 閱讀(1088) 評論(0) 收藏 舉報
浙公網安備 33010602011771號