0

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!

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
ChemEnger
  • 131
  • 12
  • Will it only have sub_batch1 and sub_batch2? Do you want to make it dynamically? – Gen Wan Nov 06 '19 at 22:48
  • It should only ever have two sub batches but there are other similar systems where more might be possible, therefore dynamic would be useful. – ChemEnger Nov 07 '19 at 08:50

2 Answers2

0

You can apply row_number() function firstly, and then use 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;

Demo

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
0

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!!

Popeye
  • 35,427
  • 4
  • 10
  • 31