sql 行转列列传行字符串转json array

最新推荐文章于 2024-04-18 21:07:05 发布

qq_43185226

最新推荐文章于 2024-04-18 21:07:05 发布

阅读量142

点赞数 2

文章标签： sql json 数据库

本文链接：https://blog.csdn.net/qq_43185226/article/details/137782949

版权

一、一行多列转一列多行：
1.select regexp_substr('a,b','[^,]+',1,level) from dual connect by regexp_substr('a,b','[^,]+',1,level)is not null;
2.行（逗号分割）转列：SELECT unnest(string_to_array(JSID, ',')) FROM Table WHERE id=234;
二、一列多行转一行一列：select listagg(BATCH_NO, ',') within group(order by BATCH_NO)  from Table   where  FLAG_TK = 0  ;
三、列转json，json转数组：SELECT '{"tranType":"50001","infoList":'||array_to_json(ARRAY((SELECT row_to_json(b.*) FROM (SELECT jzmj,tnmj,gtmj FROM Table where "bjh" = '1120117035')b))) || '}' FROM dual;