請問如何將整個Table 內欄位行列作轉置- 藍色小舖 BlueShop

台灣最大程式設計社群網站

∣免費加入會員∣登入 ∣回首頁∣ 您好

線上人數
1077

會員總數：245973
討論主題：189551

登入

登出

歡迎您免費加入會員

討論區選單

開發工具

行動裝置開發

資料庫

多媒體 / 網管

綜合

文章區

討論區列表 >> Oracle >> 請問如何將整個Table 內欄位行列作轉置

[]

[我要回覆]

請問如何將整個Table 內欄位行列作轉置
價值 : 20 QP 點閱數:1946 回應數:2

樓主

Evan

56	45
708	74

發送站內信

有個SQL table : job_list 內欄位如下

name value1 value2 value3 value4 value5 value6 ....
job a b c d e f
Sequence 1 3 2
frist V
last V V
commplete V
cancel V

不用對資料做計算或統計等運算!
只是單純將行列資料做轉置就好 (如下格式)

name job Sequence frist last commplete cancel
value1 a 1
value2 b 3 V
value3 c V
value4 d 2 V
value5 e V
value6 f V
...

有看到 pivot 相似需求語法,但是找到的都是有對table內做統計運算後再轉置的範例
(曾試著改看看,但對pivot語法不太熟,改出來的都不是自己想要的!)

請問~
能不能幫忙提供 pivot 只單純做欄位行列資料轉置的寫法,還是有其他sql語法能辦到呢??
謝謝

搜尋相關Tags的文章： [ 轉置 ] , [ pivot ] , [ ORACLE ] ,
本篇文章發表於2017-01-02 15:17

1樓

回應

QOO

不好意思，我用土法煉鋼的方式做出結果

但我沒有ORACLE環境，底下的語法是用工具轉出來的，不曉得執行後有沒有錯誤

MSSQL的部分測試過了，沒有問題

table1是模擬你的原始table，table2是輸出結果的table

PL/SQL

begin

declare @table1 table(
c_name varchar2(10),
c1 varchar2(2),
c2 varchar2(2),
c3 varchar2(2),
c4 varchar2(2),
c5 varchar2(2),
c6 varchar2(2)
)

insert into @table1 values('job', 'a', 'b', 'c', 'd', 'e', 'f');
insert into @table1 values('Sequence', '1', '3', '2','','','');
insert into @table1 values('frist', 'V','','','','','');
insert into @table1 values('last', 'V', 'V','','','','');
insert into @table1 values('commplete', 'V','','','','','');
insert into @table1 values('cancel', 'V','','','','','');

declare @table2 table(
c_name varchar2(10),
c1 varchar2(2),
c2 varchar2(2),
c3 varchar2(2),
c4 varchar2(2),
c5 varchar2(2),
c6 varchar2(2)
)

insert into @table2 values('value1','','','','','','');
insert into @table2 values('value2','','','','','','');
insert into @table2 values('value3','','','','','','');
insert into @table2 values('value4','','','','','','');
insert into @table2 values('value5','','','','','','');
insert into @table2 values('value6','','','','','','');

v_c_name varchar2(10); v_r2 varchar2(2); v_r3 varchar2(2);v_r4 varchar2(2);v_r5 varchar2(2);v_r6 varchar2(2);v_r1 varchar2(2);

@pointer sys_refcursor
set @pointer = cursor for select * from @table1 ;

open @pointer ;

FETCH NEXT FROM @pointer INTO v_c_name,v_r1,v_r2,v_r3,v_r4,v_r5,v_r6 ;

WHILE NEXT%FOUND  
loop 
if (v_c_name = 'job')
then 
  if (length(rtrim(v_r1))>0)
  then 
     update @table2 set c1= v_r1 where c_name='value1';
  end if;
  
  if (length(rtrim(v_r2))>0)
  then 
     update @table2 set c1= v_r2 where c_name='value2';
  end if;

if (length(rtrim(v_r3))>0)
  then 
     update @table2 set c1= v_r3 where c_name='value3';
  end if;

if (length(rtrim(v_r4))>0)
  then 
     update @table2 set c1= v_r4 where c_name='value4';
  end if;

if (length(rtrim(v_r5))>0)
  then 
     update @table2 set c1= v_r5 where c_name='value5';
  end if;

if (length(rtrim(v_r6))>0)
  then 
     update @table2 set c1= v_r6 where c_name='value6';
  end if;

end if;

if (v_c_name = 'Sequence')
then 
  if (length(rtrim(v_r1))>0)
  then 
     update @table2 set c2= v_r1 where c_name='value1';
  end if;
  
  if (length(rtrim(v_r2))>0)
  then 
     update @table2 set c2= v_r2 where c_name='value2';
  end if;

if (length(rtrim(v_r3))>0)
  then 
     update @table2 set c2= v_r3 where c_name='value3';
  end if;

if (length(rtrim(v_r4))>0)
  then 
     update @table2 set c2= v_r4 where c_name='value4';
  end if;

if (length(rtrim(v_r5))>0)
  then 
     update @table2 set c2= v_r5 where c_name='value5';
  end if;

if (length(rtrim(v_r6))>0)
  then 
     update @table2 set c2= v_r6 where c_name='value6';
  end if;

end if;

if (v_c_name = 'frist')
then 
if (length(rtrim(v_r1))>0)
  then 
     update @table2 set c3= v_r1 where c_name='value1';
  end if;
  
  if (length(rtrim(v_r2))>0)
  then 
     update @table2 set c3= v_r2 where c_name='value2';
  end if;

if (length(rtrim(v_r3))>0)
  then 
     update @table2 set c3= v_r3 where c_name='value3';
  end if;

if (length(rtrim(v_r4))>0)
  then 
     update @table2 set c3= v_r4 where c_name='value4';
  end if;

if (length(rtrim(v_r5))>0)
  then 
     update @table2 set c3= v_r5 where c_name='value5';
  end if;

if (length(rtrim(v_r6))>0)
  then 
     update @table2 set c3= v_r6 where c_name='value6';
  end if;
  
end if;

if (v_c_name = 'last')
then 
if (length(rtrim(v_r1))>0)
  then 
     update @table2 set c4= v_r1 where c_name='value1';
  end if;
  
  if (length(rtrim(v_r2))>0)
  then 
     update @table2 set c4= v_r2 where c_name='value2';
  end if;

if (length(rtrim(v_r3))>0)
  then 
     update @table2 set c4= v_r3 where c_name='value3';
  end if;

if (length(rtrim(v_r4))>0)
  then 
     update @table2 set c4= v_r4 where c_name='value4';
  end if;

if (length(rtrim(v_r5))>0)
  then 
     update @table2 set c4= v_r5 where c_name='value5';
  end if;

if (length(rtrim(v_r6))>0)
  then 
     update @table2 set c4= v_r6 where c_name='value6';
  end if;
end if;

if (v_c_name = 'commplete')
then 
if (length(rtrim(v_r1))>0)
  then 
     update @table2 set c5= v_r1 where c_name='value1';
  end if;
  
  if (length(rtrim(v_r2))>0)
  then 
     update @table2 set c5= v_r2 where c_name='value2';
  end if;

if (length(rtrim(v_r3))>0)
  then 
     update @table2 set c5= v_r3 where c_name='value3';
  end if;

if (length(rtrim(v_r4))>0)
  then 
     update @table2 set c5= v_r4 where c_name='value4';
  end if;

if (length(rtrim(v_r5))>0)
  then 
     update @table2 set c5= v_r5 where c_name='value5';
  end if;

if (length(rtrim(v_r6))>0)
  then 
     update @table2 set c5= v_r6 where c_name='value6';
  end if;

end if;

if (v_c_name = 'cancel')
then 
if (length(rtrim(v_r1))>0)
  then 
     update @table2 set c6= v_r1 where c_name='value1';
  end if;
  
  if (length(rtrim(v_r2))>0)
  then 
     update @table2 set c6= v_r2 where c_name='value2';
  end if;

if (length(rtrim(v_r3))>0)
  then 
     update @table2 set c6= v_r3 where c_name='value3';
  end if;

if (length(rtrim(v_r4))>0)
  then 
     update @table2 set c6= v_r4 where c_name='value4';
  end if;

if (length(rtrim(v_r5))>0)
  then 
     update @table2 set c6= v_r5 where c_name='value5';
  end if;

if (length(rtrim(v_r6))>0)
  then 
     update @table2 set c6= v_r6 where c_name='value6';
  end if;
  
  
  
  
  
end if;

FETCH NEXT FROM; @pointer INTO v_c_name,v_r1,v_r2,v_r3,v_r4,v_r5,v_r6 ;

end loop;

close @pointer ;
deallocate @pointer ;

select * from @table2;

end

下面是MSSQL

begin

declare @table1 table(
c_name varchar(10),
c1 varchar(2),
c2 varchar(2),
c3 varchar(2),
c4 varchar(2),
c5 varchar(2),
c6 varchar(2)
)

declare @table2 table(
c_name varchar(10),
c1 varchar(2),
c2 varchar(2),
c3 varchar(2),
c4 varchar(2),
c5 varchar(2),
c6 varchar(2)
)

declare @c_name varchar(10), @r2 varchar(2), @r3 varchar(2),@r4 varchar(2),@r5 varchar(2),@r6 varchar(2),@r1 varchar(2);

declare @pointer cursor
set @pointer = cursor for select * from @table1 ;

open @pointer ;

FETCH NEXT FROM @pointer INTO @c_name,@r1,@r2,@r3,@r4,@r5,@r6 ;

WHILE @@FETCH_STATUS = 0  
begin 
if (@c_name = 'job')
begin 
  if (len(@r1)>0)
  begin 
     update @table2 set c1= @r1 where c_name='value1';
  end
  
  if (len(@r2)>0)
  begin 
     update @table2 set c1= @r2 where c_name='value2';
  end

if (len(@r3)>0)
  begin 
     update @table2 set c1= @r3 where c_name='value3';
  end

if (len(@r4)>0)
  begin 
     update @table2 set c1= @r4 where c_name='value4';
  end

if (len(@r5)>0)
  begin 
     update @table2 set c1= @r5 where c_name='value5';
  end

if (len(@r6)>0)
  begin 
     update @table2 set c1= @r6 where c_name='value6';
  end

end

if (@c_name = 'Sequence')
begin 
  if (len(@r1)>0)
  begin 
     update @table2 set c2= @r1 where c_name='value1';
  end
  
  if (len(@r2)>0)
  begin 
     update @table2 set c2= @r2 where c_name='value2';
  end

if (len(@r3)>0)
  begin 
     update @table2 set c2= @r3 where c_name='value3';
  end

if (len(@r4)>0)
  begin 
     update @table2 set c2= @r4 where c_name='value4';
  end

if (len(@r5)>0)
  begin 
     update @table2 set c2= @r5 where c_name='value5';
  end

if (len(@r6)>0)
  begin 
     update @table2 set c2= @r6 where c_name='value6';
  end

end

if (@c_name = 'frist')
begin 
if (len(@r1)>0)
  begin 
     update @table2 set c3= @r1 where c_name='value1';
  end
  
  if (len(@r2)>0)
  begin 
     update @table2 set c3= @r2 where c_name='value2';
  end

if (len(@r3)>0)
  begin 
     update @table2 set c3= @r3 where c_name='value3';
  end

if (len(@r4)>0)
  begin 
     update @table2 set c3= @r4 where c_name='value4';
  end

if (len(@r5)>0)
  begin 
     update @table2 set c3= @r5 where c_name='value5';
  end

if (len(@r6)>0)
  begin 
     update @table2 set c3= @r6 where c_name='value6';
  end
  
end

if (@c_name = 'last')
begin 
if (len(@r1)>0)
  begin 
     update @table2 set c4= @r1 where c_name='value1';
  end
  
  if (len(@r2)>0)
  begin 
     update @table2 set c4= @r2 where c_name='value2';
  end

if (len(@r3)>0)
  begin 
     update @table2 set c4= @r3 where c_name='value3';
  end

if (len(@r4)>0)
  begin 
     update @table2 set c4= @r4 where c_name='value4';
  end

if (len(@r5)>0)
  begin 
     update @table2 set c4= @r5 where c_name='value5';
  end

if (len(@r6)>0)
  begin 
     update @table2 set c4= @r6 where c_name='value6';
  end
end

if (@c_name = 'commplete')
begin 
if (len(@r1)>0)
  begin 
     update @table2 set c5= @r1 where c_name='value1';
  end
  
  if (len(@r2)>0)
  begin 
     update @table2 set c5= @r2 where c_name='value2';
  end

if (len(@r3)>0)
  begin 
     update @table2 set c5= @r3 where c_name='value3';
  end

if (len(@r4)>0)
  begin 
     update @table2 set c5= @r4 where c_name='value4';
  end

if (len(@r5)>0)
  begin 
     update @table2 set c5= @r5 where c_name='value5';
  end

if (len(@r6)>0)
  begin 
     update @table2 set c5= @r6 where c_name='value6';
  end

end

if (@c_name = 'cancel')
begin 
if (len(@r1)>0)
  begin 
     update @table2 set c6= @r1 where c_name='value1';
  end
  
  if (len(@r2)>0)
  begin 
     update @table2 set c6= @r2 where c_name='value2';
  end

if (len(@r3)>0)
  begin 
     update @table2 set c6= @r3 where c_name='value3';
  end

if (len(@r4)>0)
  begin 
     update @table2 set c6= @r4 where c_name='value4';
  end

if (len(@r5)>0)
  begin 
     update @table2 set c6= @r5 where c_name='value5';
  end

if (len(@r6)>0)
  begin 
     update @table2 set c6= @r6 where c_name='value6';
  end
  
  
  
  
  
end

FETCH NEXT FROM @pointer INTO @c_name,@r1,@r2,@r3,@r4,@r5,@r6 ;

end

close @pointer ;
deallocate @pointer ;

select * from @table2;

end

本篇文章回覆於2017-02-06 17:44

== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--

2樓

回應

Venassa

參考 http://www.itread01.com/articles/1476488146.html

本篇文章回覆於2018-02-28 13:35

== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--

回覆

如要回應,請先登入.