oracle 行转列 及 pivot函数使用问题

oracle 行转列方法

  如果有这样的需求，数据库插入的有A表这样的结果，展示要变成B表这样的结果（下边的这个图是摘自别人的，我主要说pivot函数那种方式）

对于这样的需求即需要行转列了。

1、第一种即是大家常见的。这种方法也是别人的，我主要说后边的pivot函数那种方式

select [品号],sum(case when [日期]='06-08' then [数量] else 0 end) as [06-08],sum(case when [日期]='06-09' then [数量] else 0 end) as [06-09],sum(case when [日期]='06-10' then [数量] else 0 end) as [06-10],sum(case when [日期]='06-11' then [数量] else 0 end) as [06-11],sum(case when [日期]='06-12' then [数量] else 0 end) as [06-12],sum(case when [日期]='06-13' then [数量] else 0 end) as [06-13]from tbAgroup by [品号]

  通过以上方式可以实现上边A表——>B表的过程。
  速度我测试过，对于130多万的数据，假如来行转列的话，oracle数据库消耗了0.7s,总体来说还能满足我的需求，

2、第二种方式，pivot函数。

这个函数是oracle 11g的时候新加的，用起来比较方便，同样是上边的A表-----B表的转变，sql如下：

select * from tbA pivot(   sum(数量) for 日期  in ('06-08','06-09','06-10','06-11','06-12'))

  的确少写了很多东西，但是！也是有好几个注意点的。

使用行转列注意的地方

1、不管第一种还是第二种，转成列的那个日期字段，即：

  这个列，无法写活，我说的那种活是，通过内套一个sql，比如像pivot函数那种行转列方式，我们肯定会想到用下边的这种方式来写：

select * from tbA pivot(   sum(数量) for 日期  in (select 日期 from tbA))

  但是这么写直接报错！！！。必须写死才可以，好了，可能还会有人使用mybatis，打算在xml里，先把里边的查询出来的日期查完，拼成一个类似'06-08','06-09','06-10','06-11','06-12' 字符串，再塞进去，这样应该没问题了吧，然后如下这样下：

<select id="selectRowToCol" parameterType="java.util.Map" resultType="java.util.Map">    select * from tbA pivot(   sum(数量) for 日期  in (#{dateStr}))  </select>

  但是这么写还是直接报错！！！。
如果你还不死心，打算写活这个列，我使用的方法是最传统的JDBC那种方式，把sql拼好，然后

Connection conn = null;        ResultSet resultSet = null;        try{            // 1.加载驱动程序            Class.forName("oracle.jdbc.driver.OracleDriver");            // 2.获得数据库的连接            conn = DriverManager.getConnection(URL, UNAME, PWD);            conn.setAutoCommit(false);//事物开始            Statement st = conn.createStatement();            //下边的这个sql，提前拼好，然后传进去            resultSet = st.executeQuery(sql);            return resultSet;        }catch (Exception e) {            e.printStackTrace();        }finally{            try {                if(!conn.isClosed()){                    conn.close();                }            } catch (SQLException e) {                e.printStackTrace();            }        }

  这么写才可以查询到，这俩行转列，想写活这个列的方式，我就只知道这一种。

2、对于pivot函数还有几个注意的地方就是，以下边这个sql为例：

select * from tbA pivot(   sum(数量) for 日期  in (select 日期 from tbA))

 （1） [数量] 和 [日期]两个字段，必须对应于select *中的字段名，如果是别名，就必须跟别名对应。
 （2） for 和 in 这两个是配合使用，你无法将in 改为你想要的 like 或者其他的语法。安安稳稳的照人家要求写吧。