row to column data

I have table named AA. I have 3 column in it namely x,y,z . it has exactly one rows.

select * from aa;

x   y  z
10 20 30

I want output like

10
20
30

I have used below query

select x from AA union all select y from AA union all select z from AA ;

it is giving desired output. But I was told this is not feasible query. Can anyone of you provide me the best solution for it.

Solution

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

See: http://sqlfiddle.com/#!4/b274a/1/0

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

See: http://sqlfiddle.com/#!4/47f5f/2/0

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.