数据转置使用Oracle SQL查询
问题描述:
我有表和数据,如下面,数据转置使用Oracle SQL查询
Create table transpose (a number, b number, c number);
insert into transpose values (1,4,7);
insert into transpose values (2,5,8);
insert into transpose values (3,6,9);
commit;
select * from transpose;
A B C
1 4 7
2 5 8
3 6 9
问:我需要下面的输出使用SQL查询,这是possbile到 retrive数据(移调数据)?
A B C
1 2 3
4 5 6
7 8 9
请帮忙解决这个问题。
答
有两件重要的事情要考虑。
1)一个SQL表没有含义顺序,所以你必须添加order信息来唯一定义转置表的列。 (我在表中添加了一个列ID来模拟它)。
2)如果你正在寻找一个固定的数列和行的解决方案,你可以在SQL查询代码的结果见下文(对于一个动态的解决方案我不建议使用SQL)
select
(select a from transpose where id = 1) a,
(select a from transpose where id = 2) b,
(select a from transpose where id = 3) c
from dual
union all
select
(select b from transpose where id = 1) a,
(select b from transpose where id = 2) b,
(select b from transpose where id = 3) c
from dual
union all
select
(select c from transpose where id = 1) a,
(select c from transpose where id = 2) b,
(select c from transpose where id = 3) c
from dual;
。
A B C
---------- ---------- ----------
1 2 3
4 5 6
7 8 9
更新 - 解决方案与PIVOT
与枢提出@WW这里解决
with trans as (
/* add one select for each column */
select id, 'A' col_name, a col_value from transpose union all
select id, 'B' col, b col_value from transpose union all
select id, 'C' col, b col_value from transpose)
select * from trans
PIVOT (sum(col_value) col_value
for (id) in
(1 as "C1", /* add one entry for each row (ID) */
2 as "C2",
3 as "C3")
)
+0
感谢您的信息。 –
你的表* *转没有秩序。没有秩序的转置操作是否有意义? – Codo
在Oracle文档 –
@WW中查找“数据透视表”。好点子;我更新了解决方案。我假设在摆动之前,表格必须以键值格式转换。 –