Oracle 行转列
Oracle行转列第一种方式 建多张表#1.创建住院诊断表,按照日期分组,按照日期和患者id降序,生成RN字段。CREATE TABLE PALAN.TZ_ZY_DIAGASSELECT PATIENT_TYPE, DATE1, DIAG_NAME, PID, ROW_NUMBER() OVER (PARTITION BY DATE1 ORDER BY DATE1, TO_NUMBER(PID)
·
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
更多推荐
已为社区贡献2条内容
所有评论(0)