Take the data similar to the following form
The 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

How can transform into the column name for ATA_4_ITM_3 ATA_4_ITM_5... Value is 58350, CA... In this form?
The following
ATA_4_ITM_3 ATA_4_ITM_5 ATA_4_ITM_6...
CA 58350 NA

CodePudding user response:

 
SQL> 
SQL> Create table test (key varchar (20), the value varchar (10)); 
The Table created 
SQL> The begin 
2 insert into the test values (' ATA_4_ITM_3 ', '58350'); 
3 the insert into the test values (' ATA_4_ITM_5 ', 'CA'); 
4 the insert into the test values (' ATA_4_ITM_6 'and' NA '); 
5 the insert into the test values (' ATA_4_ITM_7 'and' NA '); 
6 insert into the test values (' ATA_4_ITM_8 'and' NA '); 
7 the insert into the test values (' ATA_4_ITM_4 'and' NA '); 
8 the insert into the test values (' ATA_4_ITM_9 'and' NA '); 
9 the end; 
10/
PL/SQL procedure successfully completed 
SQL> Select * from test 
2 the pivot (
3 Max (value) for key in (
4 'ATA_4_ITM_3 C3 and C4' ATA_4_ITM_4 ', 'ATA_4_ITM_5 C5, C6 ATA_4_ITM_6) 
5); 
C3 C4, C5 C6 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
58350 NA CA NA 
SQL> Drop table test purge; 
Table dropped 

SQL> 

CodePudding user response:

reference 1st floor wmxcn2000 response:

 
SQL> 
SQL> Create table test (key varchar (20), the value varchar (10)); 
The Table created 
SQL> The begin 
2 insert into the test values (' ATA_4_ITM_3 ', '58350'); 
3 the insert into the test values (' ATA_4_ITM_5 ', 'CA'); 
4 the insert into the test values (' ATA_4_ITM_6 'and' NA '); 
5 the insert into the test values (' ATA_4_ITM_7 'and' NA '); 
6 insert into the test values (' ATA_4_ITM_8 'and' NA '); 
7 the insert into the test values (' ATA_4_ITM_4 'and' NA '); 
8 the insert into the test values (' ATA_4_ITM_9 'and' NA '); 
9 the end; 
10/
PL/SQL procedure successfully completed 
SQL> Select * from test 
2 the pivot (
3 Max (value) for key in (
4 'ATA_4_ITM_3 C3 and C4' ATA_4_ITM_4 ', 'ATA_4_ITM_5 C5, C6 ATA_4_ITM_6) 
5); 
C3 C4, C5 C6 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
58350 NA CA NA 
SQL> Drop table test purge; 
Table dropped 

SQL> 

I add 'ATA_4_ITM_3, this value is not fixed, probably ATA_4_ITM_3 or ATA_4_ITM_4, may also be ATA_4_ITM_3 and ATA_4_ITM_4, or only ATA_4_ITM_3 or only ATA_4_ITM_4 is uncertain

CodePudding user response:

refer to the second floor dxy860812 response:

I add that the 'ATA_4_ITM_3 this value is not fixed, probably ATA_4_ITM_3 or ATA_4_ITM_4, may also be ATA_4_ITM_3 and ATA_4_ITM_4, or only ATA_4_ITM_3 or only ATA_4_ITM_4 uncertain

Dynamic splicing