Oracle之多行記錄變一行記錄，行變列，並排序（wmsys.wm_concat）

wmsys.wm_concat

定義：Oracle PL / SQL WM_CONCAT函式用於將來自多個行的資料聚合為單個行，從而提供與特定COMMENT_BODY關聯的資料列表。實際上，它將以逗號分隔的列表進行交叉製表。

請注意，WM_CONCAT未文件化且不受Oracle支援，這意味著它不應在生產系統中使用。 LISTAGG函式可以生成與WM_CONCAT相同的輸出，並由Oracle記錄和支援。

1. 現有資料結構

BBSDETAIL表（主表）

DETAIL_ID NOT NULL NUMBER　　主鍵
TITLE NOT NULL VARCHAR2(100)

BBSCOMMENT表（從表）

DETAIL_ID NOT NULL NUMBER　　外來鍵
COMMENT_BODY NOT NULL VARCHAR2(500)
COMMENT_TIME NOT NULL DATE

2. 實現功能一（從表多行記錄合併為一行，不要求排序）

--多行合併為一行，不要求排序
select DETAIL_ID,wmsys.wm_concat('{'||COMMENT_BODY||'}')
from BBSCOMMENT
group by DETAIL_ID;

輸出：

13859 {東西好，送貨快 },{好 },{物流有些慢 }
14938 {賣家還是挺熱心的，以後再來 },{東西不錯 }

3. 實現功能二（從表多行記錄合併為一行後，與主表做一連線）

--將上述SQL語句與主表做一個連線查詢
select bd.DETAIL_ID,TITLE,bcm.COMMENT_INFO
from BBSDETAIL bd,(select DETAIL_ID,wmsys.wm_concat('{'||COMMENT_BODY||'}') as COMMENT_INFO from BBSCOMMENT group by DETAIL_ID) bcm
where bd.DETAIL_ID=bcm.DETAIL_ID(+);

輸出：

13859 蘇泊爾電壓力鍋配件 {東西好，送貨快 },{好 },{物流有些慢 }
14938 Nike/耐克男性跑步鞋跑步 {賣家還是挺熱心的，以後再來 },{東西不錯 }

4. 實現功能三（從表多行記錄合併為一行，並按評價時間排序）

--多行合併為一行，要求排序（最新的評論在前面）
select DETAIL_ID, max(r)
from (select DETAIL_ID, wmsys.wm_concat(COMMENT_BODY||'('||to_char(COMMENT_TIME,'yyyy-mm-dd hh:mi:ss')||')')
 OVER(PARTITION BY DETAIL_ID ORDER BY COMMENT_TIME desc) r from BBSCOMMENT)
group by DETAIL_ID;

輸出：

13859 東西好，送貨快(2013-02-19 06:27:37),好(2012-01-14 02:23:46),物流有些慢(2012-01-01 12:00:25)
14938 賣家還是挺熱心的，以後再來(2011-11-27 05:28:27),東西不錯(2011-10-11 05:09:06)

5. 實現功能四（行變列：分兩列顯示從表兩種彙總結果，排序，並保證兩列中資料的對應關係）

--分兩列顯示兩種彙總結果，並排序，保證對應關係
select DETAIL_ID,COMMENT_TIME,COMMENT_BODY from (
 select
 DETAIL_ID,
  WMSYS.WM_CONCAT(to_char(COMMENT_TIME,'yyyy-mm-dd hh:mi:ss'))
          OVER(PARTITION BY DETAIL_ID ORDER BY COMMENT_TIME) COMMENT_TIME,
   WMSYS.WM_CONCAT('{'||COMMENT_BODY||'}')
          OVER(PARTITION BY DETAIL_ID ORDER BY COMMENT_TIME) COMMENT_BODY,
      row_number() OVER(PARTITION BY DETAIL_ID ORDER BY COMMENT_TIME desc) rs
  from BBSCOMMENT) where rs=1;

輸出：

13859 2013-02-19 06:27:37,2012-01-14 02:23:46,2012-01-01 12:00:25 {東西好，送貨快},{好},{物流有些慢}
14938 2011-11-27 05:28:27,2011-10-11 05:09:06 {賣家還是挺熱心的，以後再來},{東西不錯}