//原数据表结构如下：
 
产品名称    销售额  季度
奶酪          50     第一季度
奶酪          60     第二季度
啤酒          50     第二季度
啤酒          80     第四季度
 
 
//想转换成如下格式:
产品名称 第一季度销售额 第二季度销售额 第三季度销售额 第四季度销售额
奶酪          50               60           0              0
啤酒           0               50           0              80

 
//第一种方法：
 
oracle下可以用函数decode处理:

select 产品名称,
sum(decode(季度,'第一季度',销售额,0)) 第一季度销售额,
sum(decode(季度,'第二季度',销售额,0)) 第二季度销售额,
sum(decode(季度,'第三季度',销售额,0)) 第三季度销售额,
sum(decode(季度,'第四季度',销售额,0)) 第四季度销售额,
from 表名 
group by 产品名称;

 
//第二种方法 case when：
 
select 产品名称,sum(case when 季度='第一季度' then 销售额 else 0 end) as 第一季度销售额,
sum(case when 季度='第二季度' then 销售额 else 0 end) as 第二季度销售额,
sum(case when 季度='第三季度' then 销售额 else 0 end) as 第三季度销售额,
sum(case when 季度='第四季度' then 销售额 else 0 end) 第四季度销售额
from 表名 group by 产品名称;

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
 

 

 
文章目录
 
ORACLE行转列和列转行
定义
ORACLE-行转列-unpivot
数据准备
行转列使用
   
ORACLE-列转行-pivot
数据准备
列转行使用
  
 

 
ORACLE行转列和列转行
 
定义
 
行转列：把一行多列数据转换成一列多行数据
列转行：把一列多行数据转换成一行多列数据
 
ORACLE-行转列-unpivot
 
数据准备
 
-- 此表用于表示某人四个季度的收入：用于行转列测试
create table t_test1(
  v_name varchar2(50),-- 姓名
  v_age number,-- 年龄
  q_1 number,-- 第一季度
  q_2 number,-- 第二季度
  q_3 number,-- 第三季度
  q_4 number -- 第四季度
);

-- 插入数据
insert all 
into t_test1
values ('张三',23,2000,3000,4000,5000)
into t_test1
values ('李四',34,3000,4000,5000,6000)
select 1 from dual;

-- 提交数据
commit;
 
行转列使用
 
-- 行转列-复杂表述
select t.* from (-- 加个括号是为了表示括号里是个整体,便于理解
  (
    select 
      v_name,
      v_age,
      q_1,
      q_2,
      q_3,
      q_4
    from t_test1
  )
  unpivot (
    v_value -- 值列
    for v_q in(-- 类型列
      q_1 as '第一季度',-- 加默认值
      q_2 as '第二季度',-- 加默认值
      q_3 as '第三季度',-- 加默认值
      q_4-- 不加默认值
    )
  )
) t;

-- 行转列-简单表述
select * from t_test1
unpivot (
  v_value -- 值列
  for v_q in(-- 类型列
    q_1 as '第一季度',-- 加默认值
    q_2 as '第二季度',-- 加默认值
    q_3 as '第三季度',-- 加默认值
    q_4-- 不加默认值
  )
);
 
ORACLE-列转行-pivot
 
数据准备
 
-- 此表用于表示某人四个季度的收入：用于列转行测试
create table t_test2(
  v_name varchar2(50),-- 姓名
  v_age number,-- 年龄
  v_q varchar2(10),-- 季节类型列
  v_value number-- 值列
);


-- 插入数据
insert all 
into t_test2
values ('张三',23,'第一季度',2000) 
into t_test2
values ('张三',23,'第二季度',3000) 
into t_test2
values ('张三',23,'第三季度',4000) 
into t_test2
values ('张三',23,'第四季度',5000) 
into t_test2
values ('李四',34,'第一季度',3000)
into t_test2
values ('李四',34,'第二季度',4000)
into t_test2
values ('李四',34,'第三季度',5000)
into t_test2
values ('李四',34,'第四季度',6000)
select 1 from dual;

-- 提交数据
commit;
 
列转行使用
 
-- 列转行-复杂表述
select t.* from(-- 加个括号是为了表示括号里是个整体,便于理解
  (
    select 
      v_name,v_age,-- 这两个字段用于分组，相当于group by,一般只取主键
      v_q,-- 这个字段用于区分列，用于生成不同列
      v_value -- 这个字段用于汇总统计
    from t_test2
  )
  pivot (
    sum(v_value)
    for v_q in (
      '第一季度' as 第一季度,
      '第二季度' q_2,
      '第三季度' as q_3,
      '第四季度'
    )
  )
) t;

-- 列转行-简单表述
select * from t_test2
pivot(
  sum(v_value) as 后缀
  for v_q in (
    '第一季度' as 第一季度,
    '第二季度' q_2,
    '第三季度' as q_3,
    '第四季度'
  )
);