SQL> desc row_col_test
Name Type Nullable Default Comments
---- ------- -------- ------- --------
A INTEGER Y
B INTEGER Y
C INTEGER Y
D INTEGER Y
E INTEGER Y
SQL> select * from row_col_test;
A B C D E
-- -- -- -- --
1 1 1 1 1
2 2 2 2 2
3 3 3 3 3
其中列E作为标识列,结果:
属性名 MAX(值1) MAX(值2) MAX(值3)
------ ---------- ---------- ----------
a 1 2 3
b 1 2 3
c 1 2 3
d 1 2 3
select 属性名, max(值1), max(值2), max(值3)
from (select 'a' 属性名,
decode(e, 1, a, '') 值1,
decode(e, 2, a, '') 值2,
decode(e, 3, a, '') 值3
from row_col_test)
group by 属性名
union
select 属性名, max(值1), max(值2), max(值3)
from (select 'b' 属性名,
decode(e, 1, b, '') 值1,
decode(e, 2, b, '') 值2,
decode(e, 3, b, '') 值3
from row_col_test)
group by 属性名
union
select 属性名, max(值1), max(值2), max(值3)
from (select 'c' 属性名,
decode(e, 1, c, '') 值1,
decode(e, 2, c, '') 值2,
decode(e, 3, c, '') 值3
from row_col_test)
group by 属性名
union
select 属性名, max(值1), max(值2), max(值3)
from (select 'd' 属性名,
decode(e, 1, d, '') 值1,
decode(e, 2, d, '') 值2,
decode(e, 3, d, '') 值3
from row_col_test)
group by 属性名
阅读(889) | 评论(0) | 转发(0) |