sql - 无法使用 ; 将行数据转置为列 在甲骨文中分离

Question

请您说明一下我如何转置以下数据。

Create Table Pivot (Empname VARCHAR (10),
Deptno varchar(10),
Salary number (10));

Insert into Pivot (Empname, Deptno, Salary) values ('Max', '10',1000);
Insert into Pivot (Empname, Deptno, Salary) values ('David', '20',2000);
Insert into Pivot (Empname, Deptno, Salary) values ('AD', '30',3000);

所以选择查询的输出将是

EMPNAME DEPTNO SALARY
MAX     10     1000
David   20     2000
AD      30     3000

现在我们需要数据的所需格式如下：

Empname Max   David  Ad
Deptno  10    20     30
Sal     1000  2000   3000

请分享您对此的看法。

标签： sqloracleoracle11gpivot

Answer 1

一种选择是使用Conditional Aggregationwith UNION ALL：

select 'Deptno' as "Empname",
       max( case when Empname = 'Max' then to_number(Deptno) end ) as "Max",
       max( case when Empname = 'David' then to_number(Deptno) end ) as "David",
       max( case when Empname = 'AD' then to_number(Deptno) end ) as "AD"
  from pivot  
union all  
select 'Sal',
       max( case when Empname = 'Max' then Salary end ) ,
       max( case when Empname = 'David' then Salary end ) ,
       max( case when Empname = 'AD' then Salary end )
  from pivot;

或另一种选择是使用pivot子句UNION ALL：

select 'Deptno' as "Empname", max("Max") as "Max", max("David") as "David", max("AD") as "AD"
  from pivot   
  pivot( max(to_number(Deptno)) for Empname in ('Max' as "Max",'David' as "David",'AD' as "AD")) 
union all  
select 'Sal', max("Max") as "Max", max("David") as "David", max("AD") as "AD"
  from pivot   
  pivot( max(Salary) for Empname in ('Max' as "Max",'David' as "David",'AD' as "AD"));

Demo