Oracle 行转列

Oracle 专栏收录该内容
3 篇文章 0 订阅

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;

对应1

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

对应2

#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;


对应3

第二种方式 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"
));

PIVOT图片

第三种方式 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;

WM_CONCAT图片

第四种方式 LISTAGG

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

LISTAGG图片

  • 1
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值