Converting rows into Column in Oracle without any relation-CodePudding

I have a query which will fetch two rows only and I want to bring second row data into columns with different column name.

Below is the original query result.

The expected result is like Expected result.

Please help how shd I proceed, not able to figure out with PIVOT.

CodePudding user response：

Here's one option; see comments within code.

SQL> with
  2  your_query (column1, column2, column3) as
  3    -- this is what your current query returns
  4    (select 1, 'ABC', 123 from dual union all
  5     select 2, 'XYZ', 456 from dual
  6    ),
  7  temp as
  8    -- distinguish 1st from 2nd row
  9    (select y.*,
 10            row_number() over (order by column1) rn
 11     from your_query y
 12    )
 13  -- finally, cross join two rows and conditionally display columns.
 14  -- MAX is here to avoid empty "cells"
 15  select max(case when a.rn = 1 then a.column1 end) as col1,
 16         max(case when a.rn = 1 then a.column2 end) as col2,
 17         max(case when a.rn = 1 then a.column3 end) as col3,
 18         --
 19         max(case when b.rn = 2 then b.column1 end) as col4,
 20         max(case when b.rn = 2 then b.column2 end) as col5,
 21         max(case when b.rn = 2 then b.column3 end) as col6
 22  from temp a cross join temp b;

      COL1 COL       COL3       COL4 COL       COL6
---------- --- ---------- ---------- --- ----------
         1 ABC        123          2 XYZ        456

SQL>