CODEWITHSUNDEEP
mysqlsql
user2236736
user2236736

Reputation: 1

Joining column into MySQL query as a delimited list

I have a table in MySQL that looks like so:

date      | name    | status
03-13-2014,Bob Jones,Pending
03-13-2014,George Manuel,Pending
03-13-2014,Tom Grables,Pending
03-13-2014,Frankie Avalon,Approved
03-13-2014,Robert Garcia,Approved
03-14-2014,John Doe,Pending
03-14-2014,John Die,Approved
03-14-2014,John Zoe,Approved

What I am trying to do is grab date, name and status for all rows that say approved. However, I am also wanting to join this query in such a way that each row I grab also has a semicolon delimited list containing the names of those whose status is pending and falls on the same day as an approved. Thus, the output of my query would look something like this:

03-13-2014,Frankie Avalon,Approved,Bob Jones;George Manuel;Tom Grables
03-13-2014,Robert Garcia,Approved,Bob Jones;George Manuel;Tom Grables

Notice that I grabbed the approved requests for 03-13-2014 and added the requests whose status is pending and whose date matches the approved request as a column. I've been messing around with the join statement, and done my Google homework, but have yet to find a way to do this.

Any ideas would be greatly appreciated.

Upvotes: 0

Views: 70

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think this is the logic you want:

 select `date`,
         group_concat( (case when status = 'Approved' then name end) separator ';') as Approveds,
         group_concat( (case when status = 'Pending' then name end) separator ';') as Pendings
 from t
 group by `date`;

If you really do want pending only on dates where there are no approved, then you need an additional filter:

 select `date`,
         group_concat( (case when status = 'Approved' then name end) separator ';') as Approveds,
         group_concat( (case when status = 'Pending' then name end) separator ';') as Pendings
 from t
 group by `date`
 having `date` in (select `date` from t where status = 'Approved')

Upvotes: 1

John Conde
John Conde

Reputation: 219824

Look into group_concat()

SELECT
    GROUP_CONCAT('name` SEPARATOR ',') as NameList
FROM
    `tablename`
GROUP BY
    `date`

Upvotes: 1

Related Questions