JAVA中row与column_row to column data

You can use Oracle's UNPIVOT Clause

SELECT *

FROM AA

UNPIVOT (

value FOR name IN (x AS 'x', y AS 'y', z AS 'z')

);

Will yield the result

name value

---- -----

x 10

y 20

z 30

Can it be generalized for more columns? Since the name of the columns needs to be known in advance, you cannot make it automatically consider additional columns, but of course, you can add more columns manually. Example also having other columns than the value columns:

CREATE TABLE AA (

position NVARCHAR2(50),

x NUMBER(10),

y NUMBER(10),

z NUMBER(10),

t NUMBER(10)

);

INSERT INTO AA (position, x, y, z, t) VALUES ('pos 1', 10, 20, 30, 1);

INSERT INTO AA (position, x, y, z, t) VALUES ('pos 2', 11, 22, 33, 2);

INSERT INTO AA (position, x, y, z, t) VALUES ('pos 3', 34, 45, 56, 3);

You can query it with:

SELECT *

FROM AA

UNPIVOT (

value FOR coord IN (x AS 'x', y AS 'y', z AS 'z', t as 'time')

);

and get

POSITION COORD VALUE

-------- ----- -----

pos 1 x 10

pos 1 y 20

pos 1 z 30

pos 1 time 1

pos 2 x 11

pos 2 y 22

pos 2 z 33

pos 2 time 2

pos 3 x 34

pos 3 y 45

pos 3 z 56

pos 3 time 3

If you really want to consider more columns dynamically, you would have to detect the available columns and create and execute the SQL statement dynamically. You cannot do it with "pure" SQL.