Use 'group by' on one column but get data of multiple columns

Question

How can you manage to make a 'group by' on one column and still get data (the real data, not a 'sum') on the others?

Let me show an example of what I'd like to do:

Suppose Table A, with index on 'Group', a simple select * from A gives:

Group            Album
---------------- ---------------
ABBA             Waterloo
AC/DC            Back in Black
ABBA             Voulez-vous
ABBA             Super Trooper
Imagine Dragons  Night Visions
AC/DC            Highway to Hell
ABBA             The Visitors

I'd like to have the end result as following (knowing that I cannot have more than 4 albums for a group ... for now I guess):

Group            Album1          Album2          Album3          Album4
---------------- --------------- --------------- --------------- ---------------
ABBA             Waterloo        Voulez-vous     Super Trooper   The visitors
AC/DC            Back in Black   Highway to Hell Null            Null
Imagine Dragons  Night Visions   Null            Null            Null

So far, the closest I've come to make what I want is something like the following:

select tab4.GROUP,
tab1.ALBUM as PN1,
tab2.ALBUM as PN2,
tab3.ALBUM as PN3,
tab4.ALBUM as PN4
from
(
select A.GROUP, A.ALBUM
from A
where A.ROWID in 
  (select max(ROWID) from A 
  where GROUP in (select GROUP from A A group by A.GROUP having count(*) <= 4)
  group by GROUP
  )
) tab4
left join
(
select A.GROUP, A.ALBUM
from A A
where A.ROWID in 
  (select max(ROWID) from A 
  where GROUP in (select GROUP from A A group by A.GROUP having count(*) <= 3)
  group by GROUP
  )
) tab3 on tab4.GROUP = tab3.GROUP
left join
(
select A.GROUP, A.ALBUM
from A A
where A.ROWID in 
  (select max(ROWID) from A 
  where GROUP in (select GROUP from A A group by A.GROUP having count(*) <= 2)
  group by GROUP
  )
)tab2 on tab4.GROUP = tab2.GROUP
left join
(
select A.GROUP, A.ALBUM
from A A
where A.ROWID in 
  (select max(ROWID) from A 
  where GROUP in (select GROUP from A A group by A.GROUP having count(*) <= 1)
  group by GROUP
  )
) tab1 on tab4.GROUP = tab1.GROUP;

I know why the SQL request above is wrong: the max(rowid) will remain the same whatever condition on having count(*) is thrown. There could be some pivotto be used, but I sincerely don't see how can it be used as I have only one table and need to get all data.

As furter precision, I don't need the have the result table in a spcific order and I can limit myself to 4 albums because I know each 'Group' won't have more than that ... but I'd appreciate something generic.

EDIT: Ok, seems I have forgotten to clarify that I'm on Oracle 10g (damn this legacy code ^^) so newer functions like PIVOT won't work. Also, I'm not looking for a string aggregation like LISTAGG but really for separate columns.

I know you are looking into more like pivot solution, but have you checked group_concat and manage that data later at display (or whatever you need it for). (http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat) — Pete Minus, Jan 05 '15 at 17:19
@PeteMinus GROUP_CONCAT is MySQL, not Oracle. http://stackoverflow.com/questions/16771086/is-there-any-function-in-oracle-similar-like-group-concat-of-mysql — Barmar, Jan 05 '15 at 17:25
As of Oracle 11g one can use `LISTAGG()` for that purpose - for Oracle 10g one can use the undocumented aggregate function `WM_CONCAT()` or write an aggregate function of one's own. Good discussion here: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:2196162600402 — David Faber, Jan 05 '15 at 17:28
Since you mentioned 'not a sum', and the duplicate question shows summation, [here's a quick SQL Fiddle](http://sqlfiddle.com/#!4/66f30b/1) using max instead, which works for the string values. — Alex Poole, Jan 05 '15 at 17:35

score 1 · Accepted Answer · answered Jan 06 '15 at 09:34

@Alex Poole got it right: I was not only missing the equivalent of PIVOT code in 10g, but also ROW_NUMBER().

So the answer to my problem becomes as following:

select 
  tab1.group_name,
  MAX(CASE WHEN tab1.rank_number = 1 THEN tab1.album_name ELSE NULL END) AS ALBUM_1,
  MAX(CASE WHEN tab1.rank_number = 2 THEN tab1.album_name ELSE NULL END) AS ALBUM_2,
  MAX(CASE WHEN tab1.rank_number = 3 THEN tab1.album_name ELSE NULL END) AS ALBUM_3,
  MAX(CASE WHEN tab1.rank_number = 4 THEN tab1.album_name ELSE NULL END) AS ALBUM_4
from (
  select group_name, album_name,
    row_number() over (partition by group_name order by album_name) as rank_number
  from tablea
) tab1
group by tab1.group_name;

Not sure if my title is the best for the kind of problem I had, guess I'll keep it as it is since it revolves around group by as well.

score 0 · Answer 2 · answered Jan 06 '15 at 09:42

I believe this should work on 10i:

with r as (
  select
    group_,
    album,
    row_number() over (partition by group_ order by album) r
  from
    tq84_table_a
)
select
  r.group_,
  max(case when r.r=1 then r.album end) album1,
  max(case when r.r=2 then r.album end) album2,
  max(case when r.r=3 then r.album end) album3,
  max(case when r.r=4 then r.album end) album4
from
  r
group by
  r.group_;

I don't have a 10i installation at hand, right now, so I can't test it.

Use 'group by' on one column but get data of multiple columns

2 Answers2