row to column data

Question

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.

score 2 · Answer 1 · answered Jul 07 '18 at 14:24

2

Your query is fine:

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

A more efficient version is a bit longer:

select (case when n = 1 then x
             when n = 2 then y
             else z
        end) as x
from (select 1 as n from dual union all select 2 union all select 3
     ) cross join
     AA;

More recent versions of Oracle support lateral joins and apply. If you are using one, I would recommend:

select d(x)
from aa cross apply
     (select aa.x from dual union all
      select aa.y from dual union all
      select aa.z from dual
     ) d

answered Jul 07 '18 at 14:24

Gordon Linoff

1,242,037
58
646
786

cost of second query is more than first one. Can we do it using pivot table? I am not aware about cross apply..probably will be in 12 or 18c? – Jul 07 '18 at 14:33
@OnkarTiwari . . . Lateral joins are in Oracle 12+. – Gordon Linoff Jul 07 '18 at 17:47

Olivier Jacot-Descombes · Accepted Answer · 2018-07-07T15:01:40.343

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.

score 0 · Answer 3 · answered Jul 07 '18 at 14:36

0

You may use a simple UNPIVOT,

SELECT
    col
FROM
    aa UNPIVOT ( col
        FOR val
    IN ( x,
         y,
         z ) );

I presume your interviewer probably expected you to answer this?

Demo

answered Jul 07 '18 at 14:36

Kaushik Nayak

30,772
5
32
45

row to column data

3 Answers3