对表中所有查询的列做特殊处理

本文为作者自己编写，只供参考：
如果有一个需求需要对表中所有的列进行查询做trim处理，插入到新的表中做数据清洗，那么需要对每个列进行trim（column_name）这种语法处理，如果一个表的话，可以手动写，但是如果有1000张表呢，该怎么办啊？
这时候需要查询系统表，通过系统表信息sql进行拼接处理。当然如果数据库支持存储过程做循环的话那样最好了，但是有些数据库写存储过程实在不行或者太难，那我们就用sql做处理。
下边以oracle为测试：
对表test_20191124进行测试：建表语句如下：

CREATE TABLE test_20191124(NAME VARCHAR2(5),num_cnt NUMBER);

oracle中系统表在USER_TAB_COLUMNS中记录了表的详细列信息
具体转换如下，目前假设最多一个表有100个列，如果实际列超过这个自行添加

SELECT 'select ' ||
MAX(CASE WHEN a.rn =1 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =2 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =3 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =4 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =5 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =6 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =7 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =8 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =9 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =10 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =11 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =12 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =13 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =14 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =15 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =16 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =17 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =18 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =19 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =20 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =21 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =22 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =23 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =24 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =25 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =26 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =27 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =28 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =29 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =30 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =31 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =32 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =33 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =34 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =35 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =36 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =37 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =38 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =39 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =40 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =41 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =42 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =43 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =44 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =45 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =46 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =47 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =48 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =49 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =50 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =51 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =52 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =53 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =54 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =55 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =56 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =57 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =58 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =59 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =60 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =61 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =62 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =63 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =64 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =65 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =66 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =67 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =68 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =69 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =70 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =71 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =72 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =73 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =74 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =75 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =76 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =77 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =78 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =79 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =80 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =81 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =82 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =83 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =84 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =85 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =86 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =87 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =88 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =89 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =90 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =91 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =92 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =93 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =94 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =95 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =96 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =97 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =98 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =99 THEN a.col_name ||'),' END )     ||
MAX(CASE WHEN a.rn =100 THEN a.col_name ||'),' END )   ||
''''' from '  ||max(a.table_name)

  
FROM (SELECT table_name,'trim('||COLUMN_NAME AS col_name, ROW_NUMBER() OVER(ORDER BY COLUMN_ID ASC) AS rn 
  FROM USER_TAB_COLUMNS
 WHERE LOWER(TABLE_NAME) = 'test_20191124')a
 GROUP BY a.table_name;

拼写的结果如下：

select trim(NAME),trim(NUM_CNT),'' from TEST_20191124

当然如果在linux中，最后可以进行sed去把from前的’'进行去除，我这就不做单独处理了，对于表少就手动删除，如果自动程序的话，也可以用replacle 函数进行处理。
如果对其他进行特殊处理，比如coalesce，nvl等都可以，类似。