select
SUPER_SITE_ID,
split(tmp.cmapaign,',')[0] as SUPER_CMAPAIGN_ID ,
split(tmp.cmapaign,',')[2] as SUPER_POSITION
from (
select
SUPER_SITE_ID ,
cmapaign
from cjfImprLog lateral view explode( split(super_cmapaign,'\\;')) cmapaign1 as cmapaign
where yyyymmdd='${yyyymmdd}' and SUPER_TYPE =1 and super_cmapaign <> ''
SUPER_SITE_ID,
split(tmp.cmapaign,',')[0] as SUPER_CMAPAIGN_ID ,
split(tmp.cmapaign,',')[2] as SUPER_POSITION
from (
select
SUPER_SITE_ID ,
cmapaign
from cjfImprLog lateral view explode( split(super_cmapaign,'\\;')) cmapaign1 as cmapaign
where yyyymmdd='${yyyymmdd}' and SUPER_TYPE =1 and super_cmapaign <> ''
) tmp
红色部分必须要写,否则不会出现列转行的结果。
绿色部分要注意转义符号