Group by or concatenate multiple columns by multiple criteria

Question

I need to do what I thought was going to be a simple SQL query.. but I got stuck deciding how can these be grouped:

    <p>I've got the below table:</p>

Company    | Airport    |     Type
------------------------------------------
SP1        | AP1        |     ST1             
SP1        | AP1        |     ST2        
SP1        | AP1        |     ST3        
SP1        | AP2        |     ST1         
SP1        | AP2        |     ST2        
SP1        | AP2        |     ST3 
SP1        | AP3        |     ST1 
SP1        | AP3        |     ST2 
SP1        | AP4        |     ST1 
SP1        | AP4        |     ST2 
SP1        | AP4        |     ST3 
SP1        | AP4        |     ST4

I want to group AP and ST in the following way so that the desired result is like this:

(CASE 1)

SP         | AP             |     ST
------------------------------------------
SP1        | AP1, AP2, AP4  |     ST1, ST2, ST3
SP1        | AP3            |     ST1, ST2        
SP1        | AP4            |     ST4

Any thoughts? Really appreciated!

Update

As pointed out, there is another alternative for the result:

(CASE 2)

SP         | AP             |     ST
------------------------------------------
SP1        | AP1, AP2       |     ST1, ST2, ST3
SP1        | AP3            |     ST1, ST2        
SP1        | AP4            |     ST1, ST2, ST3, ST4

I've also added titles to the columns to give a bit more context. The idea is just that, to be able to group associated elements. I'm happy with any of the two results, hopefully both alternatives if possible..

possible duplicate of [Equivalent to PostgreSQL array() / array\_to\_string() functions in Oracle 9i](http://stackoverflow.com/questions/4326868/equivalent-to-postgresql-array-array-to-string-functions-in-oracle-9i) which also references http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php — Glenn, Apr 08 '14 at 14:03
Why does AP4 appear on the first output row instead of having "ST1, ST2, ST3, ST4" on the third row? — Darius X., Apr 08 '14 at 14:18
AP4 is a "special" case (CASE 1) because it has all the elements of the AP1 and AP2 group (i.e. ST1, ST2, ST3) plus an extra element ST4, that has to be shown on a separate row, as it is not part of the AP1 and AP2 group.. Hope that clarifies it! — user3510607, Apr 08 '14 at 15:10

Dmitry Nikiforov · Accepted Answer · 2014-04-08T15:27:03.790

It's not described why AP4/ST4 is a special case, but supposing you try to group withing 3 sequential elements in ST for each (sp,ap):

SQL> with t (SP, AP, ST) as (
  2  select 'SP1','AP1','ST1' from dual union all
  3  select 'SP1','AP1','ST2' from dual union all
  4  select 'SP1','AP1','ST3' from dual union all
  5  select 'SP1','AP2','ST1' from dual union all
  6  select 'SP1','AP2','ST2' from dual union all
  7  select 'SP1','AP2','ST3' from dual union all
  8  select 'SP1','AP3','ST1' from dual union all
  9  select 'SP1','AP3','ST2' from dual union all
 10  select 'SP1','AP4','ST1' from dual union all
 11  select 'SP1','AP4','ST2' from dual union all
 12  select 'SP1','AP4','ST3' from dual union all
 13  select 'SP1','AP4','ST4' from dual
 14  )
 15  select sp, listagg(ap,',') within group (order by ap) lstap, lstst
 16  from (
 17  select sp, ap, listagg(st,',') within group (order by st) lstst from (
 18  select sp, ap, st, ceil((row_number() over(partition by sp, ap order by st))/3) grp
 19  from t
 20  )
 21  group by sp, ap, grp
 22  )
 23  group by sp, lstst
 24  order by 1,2,3
 25  /

SP  LSTAP                     LSTST                                             
--- ------------------------- -------------------------                         
SP1 AP1,AP2,AP4               ST1,ST2,ST3                                       
SP1 AP3                       ST1,ST2                                           
SP1 AP4                       ST4

P.S. For alternative result output:

SQL> with t (SP, AP, ST) as (
  2  select 'SP1','AP1','ST1' from dual union all
  3  select 'SP1','AP1','ST2' from dual union all
  4  select 'SP1','AP1','ST3' from dual union all
  5  select 'SP1','AP2','ST1' from dual union all
  6  select 'SP1','AP2','ST2' from dual union all
  7  select 'SP1','AP2','ST3' from dual union all
  8  select 'SP1','AP3','ST1' from dual union all
  9  select 'SP1','AP3','ST2' from dual union all
 10  select 'SP1','AP4','ST1' from dual union all
 11  select 'SP1','AP4','ST2' from dual union all
 12  select 'SP1','AP4','ST3' from dual union all
 13  select 'SP1','AP4','ST4' from dual
 14  )
 15  select sp, listagg(ap,',') within group (order by ap) lstap, lstst
 16  from (
 17  select sp, ap, listagg(st,',') within group (order by st) lstst from (
 18  select sp, ap, st
 19  from t
 20  )
 21  group by sp, ap
 22  )
 23  group by sp, lstst
 24  order by 1,2,3
 25  /

SP  LSTAP                     LSTST                                             
--- ------------------------- -------------------------                         
SP1 AP1,AP2                   ST1,ST2,ST3                                       
SP1 AP3                       ST1,ST2                                           
SP1 AP4                       ST1,ST2,ST3,ST4

Thanks Dimitry, before I try to digest your solution.. regarding your comment "supposing you try to group within 3 sequential elements in ST". Would your SQL support an arbitrary number of STs? Which is my case: ST1, ST2... STn, and the same for AP1, AP2.. APn. — user3510607, Apr 08 '14 at 15:17
In query above I group by 3 sequential elements in ST. I also suppose there is no gaps (e.g. there is no ST1, ST2,[no ST3] ST4 case). If these conditions are in action the query should work. P.S. I just saw your alternative - this should work to you if the length of listagg output does not exceed 4000 bytes - this is a limit for SQL character functions in 11G. — Dmitry Nikiforov, Apr 08 '14 at 15:23
Ok, forgetting about "CASE 1" above (which I now think is far too complicated and the final grouping will pretty much depend on the original ordering of elements), do you have any thoughts about the "much" simpler "CASE 2", which only groups exactly identical sequences? — user3510607, Apr 08 '14 at 15:28
I just have added the example - see plese the comment above. — Dmitry Nikiforov, Apr 08 '14 at 15:29
Perfect! That's the solution I was looking for, many thanks Dimitry! — user3510607, Apr 08 '14 at 16:02

Group by or concatenate multiple columns by multiple criteria

1 Answers1