使用Oracle SQL转置表格
我在表中使用SQL转置一些数据。这里是示例数据。使用Oracle SQL转置表格
create table test_pivot(
Name varchar2(100),
DeptA varchar2(50),
DeptB varchar2(50),
DeptC varchar2(50),
DeptD varchar2(50)
);
insert all
into test_pivot(Name,DeptA,DeptB,DeptC,DeptD)
values('Asfakul','Y',NULL,NULL,NULL)
into test_pivot(Name,DeptA,DeptB,DeptC,DeptD)
values('Debmalya',NULL,'Y',NULL,NULL)
into test_pivot(Name,DeptA,DeptB,DeptC,DeptD)
values('Ranjan',NULL,NULL,'Y',NULL)
into test_pivot(Name,DeptA,DeptB,DeptC,DeptD)
values('santanu',NULL,NULL,NULL,'Y')
select 1 from dual;
我要显示像下面的数据..
我有一个艰难的时间计算出来。请告诉我。
如果您的数据库版本支持pivot和unpivot,那么您可以使用相同的。 请参见下面的查询,我想这应该帮助你..
SELECT *
FROM( SELECT *
FROM test_pivot
UNPIVOT (Check_val FOR DEPT IN (DEPTA, DEPTB, DEPTC, DEPTD))
)
PIVOT(MAX(check_val) FOR NAME IN ('Asfakul' AS Asfakul,
'Debmalya' AS Debmalya,
'Ranjan' AS Ranjan,
'santanu' AS santanu))
ORDER BY dept;
谢谢。但是我们可以使用sql Only吗? – redsoxlost 2014-10-28 11:58:25
当然可以...但性能明智的使用枢轴是有用的.. – 2014-10-28 12:00:48
我们如何才能实现使用相同的SQL ..你能指导 – redsoxlost 2014-10-28 12:10:53
这里的SELECT语句不PIVOT和UNPIVOT。正如你所看到的,它要复杂得多:
select dept,
nvl(max(case when name = 'Asfakul' then dept_val end), 'N') as Asfakul,
nvl(max(case when name = 'Debmalya' then dept_val end), 'N') as Debmalya,
nvl(max(case when name = 'Ranjan' then dept_val end), 'N') as Ranjan,
nvl(max(case when name = 'santanu' then dept_val end), 'N') as santanu
from(select name,
dept,
case when dept = 'depta' then depta
when dept = 'deptb' then deptb
when dept = 'deptc' then deptc
when dept = 'deptd' then deptd
end dept_val
from test_pivot
join(select 'depta' as dept from dual union all
select 'deptb' as dept from dual union all
select 'deptc' as dept from dual union all
select 'deptd' as dept from dual
)
on 1 = 1
)
group
by dept
order
by dept
你知道你想旋转,所以你用pivot操作符有多远?或者你还在一个不支持它的旧版本上? – 2014-10-28 11:20:55
我认为这是UNPIVOT操作。我仍在尝试。 – redsoxlost 2014-10-28 11:22:56