Oracle SQL使用笛卡尔连接、正则表达式和层次查询、union all、unpiovt 解决多列值筛选、列转行问题

看到群里有人发了这么一个问题，判断每行的数据，如果某一列的值为TRUE，那么就保留列名，最后以行数据展示。

尝试解决如下：

测试数据准备：

--建表
drop table test1 purge;

create table test1 (
c1 varchar2(20),
c2 varchar2(20),
c3 varchar2(20));

comment on column test1.c1 is '列1';
comment on column test1.c2 is '列2';
comment on column test1.c3 is '列3';

--插入测试数据
insert into test1 values ('false','true','true');
insert into test1 values ('true','false','true');
insert into test1 values ('true','true','false');
insert into test1 values ('false','false','true');
commit;


SQL> select * from test1;

C1                   C2                   C3
-------------------- -------------------- --------------------
true                 false                true
true                 true                 false
false                false                true

方法一：笛卡尔连接

--思路
表有n行m列
先看一行的情况
对于其中1行来说，它的任意一列的值都可能是true，也就是这一行可能转换成m行
判断某一行的某一列的值为ture很简单，把这些值为true的列的值，decode成的列序号，也就是第几列，后面有用
重点在于那些列值为ture的列，怎么转换为行
关键在于用到笛卡尔连接
对于任意一行，和列信息的数据字典进行笛卡尔连接，会关联出1*m行结果
这时候，就需要刚刚的列序号，和关联出来的列序号比对，如果相同，就保留这一行

--列的相关信息
select * from user_tab_columns a where a.table_name='TEST1';
--列的备注信息
select * from user_col_comments a where a.table_name='TEST1';

源表
c1    c2     c3
true false true

decode转换后
c1    c2     c3
1     null    3  --这是列序号

和列信息关联后，因为有3列，会关联出3行
c1    c2     c3  column_id
1     null    3   1               --1=1 保留
1     null    3   2               --null<>2 丢弃
1     null    3   3               --3=3 保留


--提取结果
select b.column_name,b.paraname
  from (select decode(c1, 'true', 1) c1,
               decode(c2, 'true', 2) c2,
               decode(c3, 'true', 3) c3
          from test1) a,
       (select column_name, column_id,
       decode(column_id,'1','列1','2','列2','3','列3') paraname
       --可以逐个decode，也可以关联 user_col_comments，里面有列的备注信息
          from user_tab_columns a
         where table_name = 'TEST1') b
 where a.c1 = b.column_id
    or a.c2 = b.column_id
    or a.c3 = b.column_id;


COLUMN_NAME                    PARANAME
------------------------------ ----
C1                             列1
C1                             列1
C2                             列2
C3                             列3
C3                             列3

方法二：正则表达式

将列值decode成列名后拼接成字符串，使用层次查询配合正则将字符串拆分成多行数据。重点在于使用connect_by_root语法，筛选出自关联的子节点。
select column_name
  from (with tt as (select decode(t.c1, 'true', 'c1', null) ||decode(t.c2, 'true', 'c2', null) ||decode(t.c3, 'true', 'c3', null) chr from test1 t)
         select regexp_substr(tt.chr, 'c[0-9]{1}', 1, level) column_name,
                rowid rd,
                connect_by_root rowid rootrd
           from tt
         connect by level <= 3
                and regexp_instr(tt.chr, 'c[0-9]{1}', 1, level) > 0)
          where rd = rootrd;

COLUMN_NAME
------------
c1
c3
c1
c2
c3

更正，以上有错误，当一行数据都为true，也就是3个或者以上的true拼接就回出错误结果。目前来看，以下sql可以得出正确结果。主要是使用sys_connect_by_path，这样就
可以知道每个节点的所有上级节点，只保留所有节点都一样的那个分支。
select chr, subchr
  from (select chr,
               sys_connect_by_path(chr, ',') path,
               regexp_substr(t.chr, '..', 1, level) subchr
          from (select decode(c1, 'true', 'c1', null) ||
                       decode(c2, 'true', 'c2', null) ||
                       decode(c3, 'true', 'c3', null) chr
                  from test1) t
        connect by level <= 3
               and regexp_instr(t.chr, '..', 1, level) > 0)
 where regexp_substr(path, '^(,[^,]+)\1{0,2}$') is not null;

方法三：union all

这个不用解释，简单直接，缺点在于做了3次全表扫描
select 'c1' cols, c1 VAL from test1 where c1 = 'true'
union all
select 'c2' cols, c2 from test1 where c2 = 'true'
union all
select 'c3' cols, c3 from test1 where c3 = 'true';

COLS    VAL
----    ----
c1      true
c1      true
c2      true
c3      true
c3      true

方法四：unpivot

使用系统提供的unpiovt语法，实现列转行，简洁优美
select cols, column_value
  from test1 unpivot include nulls(column_value for cols in(c1, c2, c3))
 where column_value = 'true';

COLS   COLUMN_VALUE
-------  ---------------
C1        true
C3        true
C1        true
C2        true
C3        true

总结，sql的世界，大部分情况下简洁和高效是相匹配的，熟悉oracle提供的语法，实现特定功能会达到事半功倍的效果。

关于转置函数pivot、unpivot看这里

http://blog.youkuaiyun.com/seandba/article/details/72730657