表 A (T_SMCOMMONMSG) 存儲多條消息 (FXH, FTYPE, FMSG_CONTENT),需轉為表 B (T_SMYL_COMMONSND) 的雙列寬表 (FMSG_CONTENT1, FMSG_CONTENT2):
-- 表 A 源數據
FXH FTYPE FMSG_CONTENT
98 0 msg1
99 0 msg2
100 0 msg3
101 0 msg4
-- 表 B 目標格式
FXH FMSG_CONTENT1 FMSG_CONTENT2
1 msg1 msg2
2 msg3 msg4
FTYPE=0,按 FXH 排序。ROWNUM 生成序號,奇數行歸入第 1 列,偶數行歸入第 2 列。CEIL(ROWNUM/2) 作為分組鍵,配合 MAX(CASE...) 聚合完成行轉列。INSERT INTO T_SMYL_COMMONSND (FXH, FMSG_CONTENT1, FMSG_CONTENT2)
SELECT CEIL(ROWNUM/2) AS FXH,
MAX(CASE WHEN MOD(ROWNUM,2)=1 THEN FMSG_CONTENT END) AS FMSG_CONTENT1,
MAX(CASE WHEN MOD(ROWNUM,2)=0 THEN FMSG_CONTENT END) AS FMSG_CONTENT2
FROM (
SELECT FMSG_CONTENT
FROM T_SMCOMMONMSG
WHERE FTYPE = 0
ORDER BY FXH
)
GROUP BY CEIL(ROWNUM/2);
無需 PL/SQL 循環,純 SQL 高效完成行轉列,適配報表展開、短信拆分等場景。
(本文共 3 頁,含中間結果演示與擴展變體)