create table test(
tname varchar2(100),
tvalue varchar2(100),
tcode varchar2(100),
tid varchar2(100)
);
insert into test values(1,2,3,4);
insert into test values(2,3,4,5);
select * from test;
--这下面是要实现转换后的效果,不过这个方法不太好,哪位高手能有更好的转换方法?谢谢
select 'Tname' as name ,tname as value from test where tid =4 union all
select 'tvalue',tvalue from test where tid =4 union all
select 'tcode',tcode from test where tid =4 union all
select 'tid',tid from test where tid =4
一个是列转行,一个是行转列with test as(
select 1 as tname ,2 as tvalue ,3 as tcode ,4 as tid from dual
union all
select 2 as tname ,3 as tvalue ,4 as tcode ,5 as tid from dual)
select *
from test
UNPIVOT
( value
for name in (tname,tvalue,tcode,tid)
)