列换行

--方法1：通过union或union all转换
with tdata as (
select * from table( values('张三', 90.5, 88.5, 95.5, 100), ('李四', 90.5, 88.5, 95.5, 0) ) as t(fname, fmoney8, fmoney9, fmoney10, fmoney11)
)
select fname, fmoney8 as fmoney from tdata
union
select fname, fmoney9 as fmoney from tdata
union
select fname, fmoney10 as fmoney from tdata
union
select fname, fmoney11 as fmoney from tdata
order by fname, fmoney;

--方法2：通过合并、拆分转换
with n(num) as (
values (8) --自定义开始的值
union all
select num + 1 from n where num <= 10 --比最大长度小1
)
select * from (
--将字符串的金额转换成decimal
select fname, num as fnum, cast(fmoney as decimal(12,2) ) as fmoney from (
select fname, num,
length(rtrim(char(num-1))) as "数字的长度",
locate('_'||rtrim(char(num-1))||'_',fmoney)+2+length(rtrim(char(num-1))) as "开始位置", locate('_'||rtrim(char(num))||'_',fmoney) as "结束位置" ,
(locate('_'||rtrim(char(num))||'_',fmoney) - locate('_'||rtrim(char(num-1))||'_',fmoney) - 2 - length(rtrim(char(num-1))) ) as "长度",
--拆分每个金额
substr( fmoney, (locate('_'||rtrim(char(num-1))||'_',fmoney) + 2 + length(rtrim(char(num-1))) ), (locate('_'||rtrim(char(num))||'_',fmoney) - locate('_'||rtrim(char(num-1))||'_',fmoney) - 2 - length(rtrim(char(num-1))) ) ) as fmoney
from table( values('张三', 90.5, 88.5, 95.5, 100), ('李四', 90.5, 88.5, 95.5, 0) ) as t(fname, fmoney8, fmoney9, fmoney10, fmoney11),
--将多个金额合并
--转换成字符串的时候可能要去掉右边的空串：rtrim()
--decimal转换成char的时候左边多出几个0, 处理方法：char(cast(fmoney*100 as bigint)/100)
table( values('_7_' ||char(fmoney8) ||'_8_' ||char(fmoney9) ||'_9_' ||char(fmoney10)||'_10_'||rtrim(char(fmoney11))||'_11_') ) as t2(fmoney),
n
) as temp
) as temp where fmoney <> 0 order by fname, fmoney