Oracle SQL从唯一行返回列值

Question

我有一个包含单个批次的表，可以有多个子批次：

+--------+----------+
¦Batch   +Sub-batch ¦
¦--------¦----------¦
¦B_01    ¦SB_01     ¦
¦B_02    ¦SB_02     ¦
¦B_02    ¦SB_03     ¦
¦B_03    ¦SB_04     ¦
+--------+----------+

其中B_和SB_只是批次的名称(标识符(，不需要聚合。

我想退货：

+--------+-----------+-----------+
¦Batch   +Sub_Batch1 ¦Sub_Batch2 ¦
¦--------¦-----------¦-----------¦
¦B_01    ¦SB_01      ¦           ¦
¦B_02    ¦SB_02      ¦SB_03      ¦
¦B_03    ¦SB_04      ¦           ¦
+--------+-----------+-----------+

看起来PARTITION BY或PIVOT是可行的，但我似乎只能找到聚合函数。

感谢您的帮助！

Answer 1

如果只有2个子批次，则可以简单地使用MIN和MAX聚合函数，如下所示：

SQL> WITH YOUR_TABLE(Batch, Sub_batch) AS
2  (SELECT 'B_01', 'SB_01' FROM DUAL UNION ALL
3  SELECT 'B_02', 'SB_02' FROM DUAL UNION ALL
4  SELECT 'B_02', 'SB_03' FROM DUAL UNION ALL
5  SELECT 'B_03', 'SB_04' FROM DUAL)
6  SELECT
7      BATCH,
8      MIN(SUB_BATCH) AS SUB_BATCH1,
9      CASE
10          WHEN MIN(SUB_BATCH) <> MAX(SUB_BATCH) THEN MAX(SUB_BATCH)
11      END AS SUB_BATCH2
12  FROM YOUR_TABLE
13  GROUP BY BATCH
14  ORDER BY BATCH;
BATCH      SUB_BATCH1 SUB_BATCH2
---------- ---------- ----------
B_01       SB_01
B_02       SB_02      SB_03
B_03       SB_04
SQL>

干杯！！

Answer 2

您可以先应用row_number()函数，然后使用conditional aggregation:

with t as
(
select t0.*,
row_number() over (partition by Batch order by Sub_batch) as rn
from t0
)
select Batch,
max(case when rn = 1 then Sub_batch end) as Sub_Batch1, 
max(case when rn = 2 then Sub_batch end) as Sub_Batch2
from t
group by Batch
order by Batch;

演示