行列转换问题总结

Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4  

行列转换问题

在制作报表或查询数据的时候，我们经常可能会遇到的一个问题是行列换。概念很简单，行转列就是将一行中指定的N个字段转为N条记录。反过来便是列转行了。

一、行转列

有如下几个部门，分别对应了若干个员工：

select dname, e.ename

  from scott.emp e, scott.dept d

 where e.deptno = d.deptno

 

数据如下：

DNAME	ENAME
RESEARCH	SMITH
SALES	ALLEN
SALES	WARD
RESEARCH	JONES
SALES	MARTIN
SALES	BLAKE
ACCOUNTING	CLARK
RESEARCH	SCOTT
ACCOUNTING	KING
SALES	TURNER
RESEARCH	ADAMS
SALES	JAMES
RESEARCH	FORD
ACCOUNTING	MILLER

 

 

现在要求按一个部门一个字段列出所有员工，如下进行显示：

RN	ACCOUNTING	RESEARCH	SALES
1	CLARK	JONES	WARD
2	KING	FORD	TURNER
3	MILLER	ADAMS	ALLEN
4		SMITH	JAMES
5		SCOTT	BLAKE
6			MARTIN

 

应该如何实现？

首先，我们取出每个部门的名称（或其他唯一性的编号），然后对每一条记录进行识别。当遇到部门名称为“accounting”时，即显示该名称，否则不显示。其他依次类推。于是，就有如下语句：

select  dname,

       decode(d.dname, 'ACCOUNTING', e.ename) accounting,

       decode(d.dname, 'RESEARCH', e.ename) RESEARCH,

       decode(d.dname, 'SALES', e.ename) SALES

  from  scott.emp e, scott.dept d

 where  e.deptno = d.deptno

 order  by dname

 

 

 

 

结果如下：

DNAME	ACCOUNTING	RESEARCH	SALES
ACCOUNTING	CLARK
ACCOUNTING	KING
ACCOUNTING	MILLER
RESEARCH		SCOTT
RESEARCH		ADAMS
RESEARCH		FORD
RESEARCH		JONES
RESEARCH		SMITH
SALES			JAMES
SALES			TURNER
SALES			BLAKE
SALES			MARTIN
SALES			ALLEN
SALES			WARD

 

可以从结果中看出，每当遇到一条符合"accounting"的记录时，他对应的其他两个部门的字段就变为空了，那么，该如何将这些空白补足，并按照最大行数显示呢？比如，accounting部门是3条，research是5条，sales是6条，结果就要按照6行来显示。

这时，我们就要借助row_number这个分析函数。首先给每个部门的员工按1-N进行编号，编号

依据任意：

select  dname,

       row_number() over(partition by dname order by dname) rn,

       decode(d.dname, 'ACCOUNTING', e.ename) accounting,

       decode(d.dname, 'RESEARCH', e.ename) RESEARCH,

       decode(d.dname, 'SALES', e.ename) SALES

    from  scott.emp e, scott.dept d

   where  e.deptno = d.deptno

于是我们得到了这样的数据：

DNAME	RN	ACCOUNTING	RESEARCH	SALES
ACCOUNTING	1	CLARK
ACCOUNTING	2	KING
ACCOUNTING	3	MILLER
RESEARCH	1		JONES
RESEARCH	2		FORD
RESEARCH	3		ADAMS
RESEARCH	4		SMITH
RESEARCH	5		SCOTT
SALES	1			WARD
SALES	2			TURNER
SALES	3			ALLEN
SALES	4			JAMES
SALES	5			BLAKE
SALES	6			MARTIN

 

可能以上数据还不是很明显，但是基本上可以看出个端倪。后面就是合并的过程了。由于数据有一个很明显的特征，就是每个相同的编号组对应只有一个员工，其他都为空，因此按编号合并的时候，每个编号也只有一个员工。

按照这样的思路，只要将编号相同的记录合并到一起即可。这个合并，即可以采用wmsys.wm_concat函数，也可以使用max这样的函数。以下就是实现的语句：

select rn,

       max(accounting) accounting,

       max(RESEARCH) RESEARCH,

       max(SALES) SALES

  from (select dname,

               row_number() over(partition by dname order by dname) rn,

               decode(d.dname, 'ACCOUNTING', e.ename) accounting,

               decode(d.dname, 'RESEARCH', e.ename) RESEARCH,

               decode(d.dname, 'SALES', e.ename) SALES

          from scott.emp e, scott.dept d

         where e.deptno = d.deptno)

 group by rn

 order by rn

 

下面数据就是我们想要的结果：

RN	ACCOUNTING	RESEARCH	SALES
1	CLARK	JONES	WARD
2	KING	FORD	TURNER
3	MILLER	ADAMS	ALLEN
4		SMITH	JAMES
5		SCOTT	BLAKE
6			MARTIN

 

二、列转行

列转行的思路有两种：

第一种是按字段拆分成若干部分，然后再union all。比如说，拆分成三个部分，然后再union all。

第二种是构造跟所需列数相同的行。比如说，有3个列要转成行，则构造一个三行的虚表。

 

假设有一只基金，该基金的投资组成有股票、债券、现金、权证、其他这几个类型，其中每个类型对应的值为当前市值，都对应一个字段。如下表：

基金代码	股票	债券	权证	其他
200001	5	3	7	12
200002	13	5	6	2

 

问，如何显示成如下形式的数据：

基金代码	投资类型	投资市值
200001	股票	5
200001	债券	3
200001	权证	7
200001	其他	8
200002	股票	13
200002	债券	5
200002	权证	6
200002	其他	5

 

以第一种方式实现，就是简单的union all合并：

select fp.fcode 基金代码, '股票' 投资类型, fp.stock 投资市值

  from fundportfolio fp

union all

select fp.fcode 基金代码, '债券' 投资类型, fp.bonds 投资市值

  from fundportfolio fp

union all

select fp.fcode 基金代码, '权证' 投资类型, fp.warnt 投资市值

  from fundportfolio fp

union all

select fp.fcode 基金代码, '其他' 投资类型, fp.other 投资市值

  from fundportfolio fp

 

这种写法的缺点是如果类型较多，就会很多基本大同小异的SQL union all起来，代码上显得很拖沓。那如何写出一个简洁的呢？答案就是用第二种方法，构造和转换列数相同的行数的虚拟表，然后两表做完全笛卡尔积。假设原表有N行，需要转换的字段有M个，即每行要衍生出M行，所以结果集就有N×M行，这个就是构造这个虚拟表的原理。下面语句即实现了这个原理：

select fp.fcode 基金代码,

       decode(r, 1, '股票',   2, '债券',   3, '权证',   4, '其他') 投资类型,

       decode(r, 1, fp.stock, 2, fp.bonds, 3, fp.warnt, 4, fp.other) 投资是指

  from fundportfolio fp, (select rownum r from dual connect by rownum <= 4)

即实现了所要求的数据。

 

 

 

另外，这个用法还有一个衍生的用途。

假设有数据：

begin	end
1	3
2	5

 

现要生成如下的数据：

begin	end	sn
1	3	1
1	3	2
1	3	3
2	5	2
2	5	3
2	5	4
2	5	5

 

是不是跟上面的需求很像？原理上，就是一行扩展成多行，但是跟前面的情况不一样的地方是，前面扩展的行数是固定的，而这里要扩展的行数是动态的，其扩展依据是由begin和end之间的差值决定的。那如何获取这个动态行数或者说控制结果按照所需的行数就是一个关键的问题。

首先，按照上面第二种方法实现。在这里简要的说就是最大数过滤法。首先，取出begin和end之间差值最大的数值N，然后构造一个N+1行的表，表内有一个字段s，序号为1..N+1，再与原表做笛卡尔积，最后按照s<=end过滤多余的行。SQL如下：

with tmp as(

select 1 begin, 3 end from dual union all

select 2, 5 from dual)

select begin, end, begin + r - 1

  from tmp, (select   rownum r from (select   max(end - begin)+1 n from tmp) connect by rownum <= n)

 where r <= end

 order by 1,3

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12932950/viewspace-672551/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12932950/viewspace-672551/