取的資料類似以下形式
key value
ATA_4_ITM_3 58350
ATA_4_ITM_5 CA
ATA_4_ITM_6 NA
ATA_4_ITM_7 NA
ATA_4_ITM_8 NA
ATA_4_ITM_4 NA
ATA_4_ITM_9 NA
如何能變換成 列名為ATA_4_ITM_3，ATA_4_ITM_5 ...值為58350，CA .....這種形式？
如下
ATA_4_ITM_3 ATA_4_ITM_5 ATA_4_ITM_6 ....
58350 CA NA
uj5u.com熱心網友回復：
SQL>
SQL> create table test(key varchar(20), value varchar(10));
Table created
SQL> begin
2 insert into test values('ATA_4_ITM_3', '58350');
3 insert into test values('ATA_4_ITM_5', 'CA');
4 insert into test values('ATA_4_ITM_6', 'NA');
5 insert into test values('ATA_4_ITM_7', 'NA');
6 insert into test values('ATA_4_ITM_8', 'NA');
7 insert into test values('ATA_4_ITM_4', 'NA');
8 insert into test values('ATA_4_ITM_9', 'NA');
9 end;
10 /
PL/SQL procedure successfully completed
SQL> select * from test
2 pivot (
3 max(value) for key in(
4 'ATA_4_ITM_3' C3,'ATA_4_ITM_4' C4,'ATA_4_ITM_5' C5,'ATA_4_ITM_6' C6)
5 );
C3 C4 C5 C6
---------- ---------- ---------- ----------
58350 NA CA NA
SQL> drop table test purge;
Table dropped
SQL>
uj5u.com熱心網友回復：
我補充一下，‘ATA_4_ITM_3’ 這種值不是固定的，可能是ATA_4_ITM_3 或ATA_4_ITM_4 ，也可能是ATA_4_ITM_3 和ATA_4_ITM_4，或者只有ATA_4_ITM_3 或者只有ATA_4_ITM_4 都不確定的
uj5u.com熱心網友回復：
動態拼接就可以了
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/107659.html
標籤：開發