I have a table with individual batches which can have multiple sub-batches:
+--------+----------+
¦Batch +Sub-batch ¦
¦--------¦----------¦
¦B_01 ¦SB_01 ¦
¦B_02 ¦SB_02 ¦
¦B_02 ¦SB_03 ¦
¦B_03 ¦SB_04 ¦
+--------+----------+
Where B_ and SB_ are only names (identifiers) of batches, requiring no aggregation.
I would like to return:
+--------+-----------+-----------+
¦Batch +Sub_Batch1 ¦Sub_Batch2 ¦
¦--------¦-----------¦-----------¦
¦B_01 ¦SB_01 ¦ ¦
¦B_02 ¦SB_02 ¦SB_03 ¦
¦B_03 ¦SB_04 ¦ ¦
+--------+-----------+-----------+
It looks like PARTITION BY or PIVOT is the way to go but I can only seem to find aggregate functions.
Any help gratefully received!
If there are only 2 sub_batches then you can simply use MIN and MAX aggregate functions as following:
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>
Cheers!!