grouping multiple fields using sql

Question

After doing some joins and stuff I have table in the following format

name    session_id   status  time
abc         1          10
xyz         2          11 
jack        2          10
zuck        1          10 
paul        1          10

Now I want my results to be grouped like this

session_id   name+status                   time
1            abc:10, zuck:10, paul:10
2            xyz:11, jack:11

and the result set to be sorted by time, I'm little confused how to achieve this using group by

My db is Mysql... I'm little lost on what to try... I'm trying to do group by session_id but that won't give results in required format — user401445
– user401445, Commented Nov 12, 2011 at 17:17

ComfortablyNumb · Accepted Answer · 2011-11-12 16:38:31Z

4

if your db is oracle, you can try. Afer oracle 11g r2, you can also use listagg

select session_id, wm_concat(name_status), time from 
(
  select session_id, (name+':'+status) as name_status, time
  from stuff
)
group by session_id
order by time

answered Nov 12, 2011 at 16:38

ComfortablyNumb

3,5212 gold badges17 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ComfortablyNumb Over a year ago

then use group_concat. dev.mysql.com/doc/refman/5.0/en/…

user401445 Over a year ago

Thanks that is exactly what I need but I'm having one more problem... my sql is something like group_concat(u.firstname, \' \', u.lastname, \':\', s.punctuality_status) but this s.punctuality_status can be null in some cases... is there a way in which I can specify some default like -1 in case it is null as no result is showing up in such a case

ypercubeᵀᴹ Over a year ago

@user401445: Use COALESCE(status, -1) instead of status

ypercubeᵀᴹ · Accepted Answer · 2011-11-12 23:56:40Z

2

SELECT
    session_id
  , GROUP_CONCAT( CONCAT(name, ':', COALESCE(status, -1) 
                  SEPARATOR ', '
                  ORDER BY `time`
                )  AS name_status
  , MIN(`time`)    AS min_time     --- or MAX(`time`)
FROM 
    TableX
GROUP BY
    session_id
ORDER BY
    min_time

You could use time instead of MIN(time), if all rows with same session_id have same time (if time is functionally dependent on session_id). If that's not the case and you use time, you'll not get consistent results on that column.

answered Nov 12, 2011 at 23:56

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Collectives™ on Stack Overflow

grouping multiple fields using sql

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related