你可以用一些字符串连接所有的列,用一些常量如'null'替换NULL,提取null部分,反转非null部分,
再次用null连接reversed not null,分割得到一个数组,现在你可以得到数组元素作为列,不要忘记判断null并反转每个元素以正确地得到长于1个字符的值:
with
test_data as (
select 'v' as dim, 'c' as col1, 'b' as col2, 'a' as col3, NULL as col4 union all
select 'x', 'a', NULL, NULL , NULL union all
select 'y', 'b', 'a', NULL , NULL union all
select 'z', 'd', 'c', 'b', 'a'
)
select dim,
case when reversed_array[0] = 'null' then null else reverse(reversed_array[0]) end as col1,
case when reversed_array[1] = 'null' then null else reverse(reversed_array[1]) end as col2,
case when reversed_array[2] = 'null' then null else reverse(reversed_array[2]) end as col3,
case when reversed_array[3] = 'null' then null else reverse(reversed_array[3]) end as col4
from
(
select dim,
split(
concat(
--all before |null
reverse(regexp_replace(concat_ws('|',nvl(col1,'null'), nvl(col2,'null'), nvl(col3,'null'), nvl(col4,'null')),'(\\|null)*','')),
--NULLs
regexp_extract(concat_ws('|',nvl(col1,'null'), nvl(col2,'null'), nvl(col3,'null'), nvl(col4,'null')),'((\\|null)*)$',1)
)
,'\\|') as reversed_array
from test_data
) s
结果:
dim col1 col2 col3 col4
v a b c NULL
x a NULL NULL NULL
y a b NULL NULL
z a b c d