Oracle 列转换为行, 逗号拼接.

最新推荐文章于 2020-09-19 16:48:16 发布

转载最新推荐文章于 2020-09-19 16:48:16 发布 · 161 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/liqb/p/3472524.html

文章标签：

#数据库

CREATE OR REPLACE VIEW V_IMD_JC_BAD_WEATHER AS
SELECT
EVENT_ID,
TRANSLATE (LTRIM (text, '/'), '*/', '*,') BWS_SEC_ID,
TRANSLATE (LTRIM (mse_name, '/'), '*/', '*,') MSE_NAME
FROM
    (SELECT ROW_NUMBER () OVER (PARTITION BY EVENT_ID ORDER BY EVENT_ID,lvl DESC) rn,EVENT_ID, text,mse_name
     FROM (SELECT EVENT_ID, LEVEL lvl,
                              SYS_CONNECT_BY_PATH (MSE_NAME,'/') mse_name,
                              SYS_CONNECT_BY_PATH (BWS_SEC_ID,'/') text
           FROM (SELECT   T1.EVENT_ID, T1.BWS_SEC_ID as BWS_SEC_ID,T2.MSE_NAME,
                 ROW_NUMBER () OVER (PARTITION BY T1.EVENT_ID ORDER BY T1.EVENT_ID,T1.BWS_SEC_ID) x
                 FROM IMD_JC_BAD_WEATHER_SECTION T1
                 LEFT JOIN IMD_COMM_MAIN_SECTION T2 ON T1.BWS_SEC_ID = T2.MSE_ID
                 ORDER BY EVENT_ID, BWS_SEC_ID) a
     CONNECT BY EVENT_ID = PRIOR EVENT_ID AND x - 1 = PRIOR x))
WHERE rn = 1
ORDER BY EVENT_ID;