Oracle 行转列

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