在Oracle中将行转换为列，没有任何关系

Question

我有一个查询，将获取两行，我想把第二行数据与不同的列名的列。

如下为原始查询结果。

预期结果如下预期的结果。

请帮助我如何进行，无法与PIVOT弄清楚。

Answer 1

这里有一个选择;参见代码中的注释。

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>