Oracle 行转列

第一种方式 建多张表

#1.创建住院诊断表,按照日期分组,按照日期和患者id降序,生成RN字段。
#注意："DATE1"可以去掉，因为前面已经按照"DATE1"分区了，下面的几种方式也是一样。
CREATE TABLE PALAN.TZ_ZY_DIAG
AS
SELECT PATIENT_TYPE, DATE1, DIAG_NAME, PID
	, ROW_NUMBER() OVER (PARTITION BY DATE1 ORDER BY TO_NUMBER(PID) DESC) AS RN
FROM PALAN.IN_DIAG_TOP50;

#2.创建1~50的RN表。
CREATE TABLE PALAN.NUM_LIST
AS
SELECT RN
FROM PALAN.TZ_ZY_DIAG
WHERE DATE1 = '2018-11'
ORDER BY RN;

#3.去重日期后,72个月,关联RN 1~50的"NUM_LIST"表,按照日期和RN升序(不写默认升序)3600条记录
WITH TMP_LIST AS 
(SELECT T1.DATE1,T2.RN FROM 
(SELECT DISTINCT DATE1 FROM  PALAN.TZ_ZY_DIAG) T1 JOIN  PALAN.NUM_LIST T2 ON 1=1 ORDER BY 1,2),

#住院诊断表关联"TMP_LIST"表,条件：日期等于日期,RN等于RN,按照日期分组,按照日期升序。
TMP_LIST2 AS 
(SELECT 
TL.DATE1,
MAX(DECODE(TL.RN,'1',TZD.DIAG_NAME||'---'||TZD.PID)) AS "1",
MAX(DECODE(TL.RN,'2',TZD.DIAG_NAME||'---'||TZD.PID)) AS "2",
MAX(DECODE(TL.RN,'3',TZD.DIAG_NAME||'---'||TZD.PID)) AS "3",
-- 代码太长，此处省略(4-49)
MAX(DECODE(TL.RN,'50',TZD.DIAG_NAME||'---'||TZD.PID)) AS "50"

FROM PALAN.TZ_ZY_DIAG TZD 
JOIN  
TMP_LIST TL 
ON TZD.DATE1 = TL.DATE1 
AND TZD.RN = TL.RN
GROUP BY TL.DATE1 ORDER BY TL.DATE1 )

SELECT * FROM TMP_LIST2;

第二种方式 PIVOT

#第二种方式 PIVOT
WITH TEMP AS(
SELECT DATE1,DIAG_NAME||'---'||PID AS DIAG_NAME_PID,ROW_NUMBER() OVER(PARTITION BY DATE1 
ORDER BY TO_NUMBER(PID) DESC) AS RN FROM PALAN.IN_DIAG_TOP50
)
SELECT * FROM (SELECT DATE1,DIAG_NAME_PID,RN FROM TEMP)PIVOT(MAX(DIAG_NAME_PID) FOR RN IN (

'1' AS "1",
'2' AS "2",
'3' AS "3",
-- 代码太长，此处省略(4-49)
'50' AS "50"
));

第三种方式 WM_CONCAT

#第三种方式 WM_CONCAT
SELECT DATE1,MAX(RN) FROM 
(SELECT DATE1,WM_CONCAT(DIAG_NAME||'---'||PID) OVER(PARTITION BY DATE1 ORDER BY TO_NUMBER(PID) DESC) AS RN 
FROM PALAN.IN_DIAG_TOP50)
GROUP BY DATE1;

第四种方式 LISTAGG

#第四种方式 LISTAGG
SELECT DATES, LISTAGG(DIAG_NAME||'---'||PID, '	')  WITHIN GROUP (ORDER BY TO_NUMBER(PID) DESC) 
 FROM SYSTEM.IN_DIAG_TOP50  GROUP BY DATES

1.行转列

（1）decode实现

SELECT t_year,
MAX(DECODE(t_month,'1月',t_value)) AS 一月,
MAX(DECODE(t_month,'2月',t_value)) AS 二月,
MAX(DECODE(t_month,'3月',t_value)) AS 三月,
MAX(DECODE(t_month,'4月',t_value)) AS 四月
FROM a_testtable WHERE t_month IN('1月','2月','3月','4月')
GROUP BY t_year;

 （2）case when实现

SELECT t_year,
MAX(CASE t_month WHEN '1月' THEN t_value END) AS 一月,
MAX(CASE t_month WHEN '2月' THEN t_value END) AS 二月,
MAX(CASE t_month WHEN '3月' THEN t_value END) AS 三月,
MAX(CASE t_month WHEN '4月' THEN t_value END) AS 四月
FROM a_testtable WHERE t_month IN('1月','2月','3月','4月')
GROUP BY t_year;

 （3）pivot函数

SELECT * FROM a_testtable
PIVOT(SUM(t_value) FOR t_month IN('1月' AS 一月,'2月' AS 二月,'3月' AS 三月,'4月' AS 四月));

SELECT * FROM 
(SELECT t_year AS ye,t_month AS mon,t_value AS val FROM a_testtable) a 
PIVOT(SUM(val) FOR mon IN('1月' AS 一月,'2月' AS 二月,'3月' AS 三月,'4月' AS 四月));

 2列转行

准备表及数据

 （1）decode实现

SELECT t_year,
DECODE(lvl,1,'1月',2,'2月',3,'3月',4,'4月') AS t_month,
DECODE(lvl,1,one_m,2,two_m,3,tree_m,4,four_m) AS t_qty
FROM b_testtable,(SELECT LEVEL lvl FROM dual CONNECT BY LEVEL<=4)
ORDER BY t_year,t_month;

（2）case when实现

 SELECT t_year,
CASE lvl WHEN 1 THEN '1月' 
    WHEN 2 THEN '2月' 
    WHEN 3 THEN '3月' 
    WHEN 4 THEN '4月' 
END AS t_month,
CASE lvl WHEN 1 THEN one_m 
    WHEN 2 THEN two_m 
    WHEN 3 THEN tree_m 
    WHEN 4 THEN four_m 
END AS t_qty
FROM b_testtable,(SELECT LEVEL lvl FROM dual CONNECT BY LEVEL<=4)
ORDER BY t_year,t_month;

（3）unpivot函数实现

SELECT * FROM b_testtable
UNPIVOT(t_qty FOR t_month IN(one_m AS '1月',two_m AS '2月',tree_m AS '3月',four_m AS '4月'))
ORDER BY t_year,t_month;

oracle行转列（感觉不该叫这个名字）小记录
一、工具
win10、oracle 11g（之前版本不支持pivot函数）、pl/sql Developer

建表语句

create table r2c(
name varchar2(30),
subject  varchar2(20),
score     number
);

插入数据语句

insert into r2c values('kevin','语文',80);
insert into r2c values('kevin','数学',81);
insert into r2c values('kevin','英语',82);
insert into r2c values('tom','语文',80);
insert into r2c values('tom','数学',81);
insert into r2c values('tom','英语',82);
insert into r2c values('snake','语文',90);
insert into r2c values('snake','数学',91);
insert into r2c values('snake','英语',92);
commit;

查询结果显示

1、普通的行转列（max与decode结合）：

--就是按照要输出的样式进行重写（原表进行“转置”）
--将原来的表重写为列名为 name  语文  数学  英语  这样式的
--decode用case when then代替也可以
select name,
       max(decode(subject, '语文', score, null)) as "语文", --双引号或者直接中文，单引号会报错
       max(decode(subject, '数学', score, null)) as "数学", --用max这个聚合函数是为了配合group by
       max(decode(subject, '英语', score, null)) as "英语"
  from r2c
 group by name

2、调用pivot函数

--pivot语法：pivot(任一聚合函数 for 需转列的值所在列名 in (需转为列名的值))；
select *
  from r2c
pivot(max(score)                 --转换行里需要显示的数据，一般是数字的那列
   for subject in('语文' as "语文", '数学' as "数学", '英语' as "英语"));
   --重新定义列名

两个运行的结果截图都是

pivot函数差不多就是就干了一个把第一列数据进行分组（就是去重），然后将原表中需要转换的列进行一下转置，再切分转置后的表，这样的工作。
-