行列转换的SQL语句

数据类型

  Oracle数据库。

行转列

  表名：CM_IDVALUE ，原始数据：

ID	TEXTVALUE	TYPE
12346	A,B,C,D	String
12346	1111111	NUMBER

方案一

with a as
(select t.ID,t.TYPE,t.TEXTVALUE from CM_IDVALUE t where t.TYPE='String'),
b as
(select t.ID,t.TYPE,t.TEXTVALUE from CM_IDVALUE t where t.TYPE='NUMBER')
select t1.ID,t1.TEXTVALUE String,t2.ID,t2.TEXTVALUE NUMBER from
a  t1 left outer join b t2  on t1.ID = t2.ID
where t1.ID='12346'

方案二

select *
from (
    select ID,TEXTVALUE,TYPE from CM_IDVALUE)
  pivot (
    sum(TEXTVALUE) for TYPE in('String'STRING,'NUMBER'NUMBER ));
    /*注释：若sum()函数报错，则使用max()函数替换。*/

效果：

ID	STRING	NUMBER
12346	A,B,C,D	1111111

列转行

  表名：CM_IDVALUE ，原始数据：

ID	TEXTVALUE
12346	A,B,C,D

可用union all来实现，将ID和TEXTVALUE两列转成多行显示，SQL语句如下：

select to_char(id) LINE1 from CM_IDVALUE where id='12346'
union all
select TEXTVALUE from CM_IDVALUE where id='12346';

效果：

LINE1
12346
A,B,C,D

其它

单列转多列

  表名：CM_IDVALUE ，原始数据：

ID	TEXTVALUE
12346	A,B,C,D

根据分号将TEXTVALUE 列拆分成LINE1、LINE2、LINE3、LINE4列显示，SQL语句如下：

SELECT T.ID,
REGEXP_SUBSTR(T.TEXTVALUE,'[^,]+',1) AS LINE1,
REGEXP_SUBSTR(T.TEXTVALUE,'[^,]+',3) AS LINE2,
REGEXP_SUBSTR(T.TEXTVALUE,'[^,]+',5) AS LINE3,
REGEXP_SUBSTR(T.TEXTVALUE,'[^,]+',7) AS LINE4
FROM CM_IDVALUE T WHERE T.ID='12346';

效果：

ID	LINE1	LINE2	LINE3	LINE4
12346	A	B	C	D

多列转单列

  表名：CM_IDVALUE ，原始数据：

ID	LINE1	LINE2	LINE3	LINE4
12346	A	B	C	D

将上述LINE1、LINE2、LINE3、LINE4列合并成TEXTVALUE 列，SQL语句如下：

SELECT ID,LINE1||','||LINE2||','||LINE3||','||LINE4  as TEXTVALUE 
FROM  CM_IDVALUE T 
WHERE T.ID='12346';

效果：

ID	TEXTVALUE
12346	A,B,C,D