0

I have the following sql query:

 SELECT gp.group_name as `Group Name`,
   CONCAT(lc.first_name, ' ', lc.last_name) as `Client Names`
FROM `group` gp
INNER JOIN cbo ON (gp.cbo_cbo_id = cbo.cbo_id)
INNER JOIN credit_officer_has_cbo cocbo ON (cbo.cbo_id = cocbo.cbo_cbo_id)
INNER JOIN credit_officers co ON (cocbo.credit_officers_id_credit_officer = co.id_credit_officer)
INNER JOIN user usr ON (co.user_id = usr.id)
INNER JOIN branches br ON (co.branches_br_id = br.br_id)
INNER JOIN client_has_group chg ON (chg.group_gr_id = gp.gr_id) 
INNER JOIN loan_client lc ON (chg.loan_client_id = lc.id)

To Generate the following output

  +-----------+-------------+
  | Group Name| Client Name |
  +-----------+------+------+
  |  Group A  | Joan        |
  |  Group A  | Patrick     |
  |  Group A  | Smith       |
  |  Group B  | Samuel      |
  |  Group B  | Elise       |
  |  Group C  | Marco       |
  +-----------+-------------+ 

But i need to arrange the 'Client name as rows' as below.

+----------+-------------+-------------+-------------+
|Group Name|   Member 1  |   Member 2  |    Member 2 |
+----------+-------------+-------------+-------------+
|Group A   |   Joan      |   Patrick   |    Smith    |
+----------+-------------+-------------+-------------+
|Group B   |   Samuel    |   Elise     |             |
+----------+-------------+-------------+-------------+
|Group C   |   Marco     |             |             |
+----------+-------------+-------------+-------------+

As you can see the 'Client Name' becomes individual columns. The number of 'Member' columns is unpredictable. I couldn't find a break through to solve this query. Any help would be much appreciated. Thanks in advance!

  • The purpose of a database is not to format it pretty for you. That's the job of your application. – tombom May 7 '18 at 9:07
  • The structure of records returned by SQL query is always deterministic. If You need to obtain "dynamic" record structure, You can realize it in Stored Procedure format using Dynamic SQL (prepared statement) in it, of course. But I think that client-side formatting is the best way. – Akina May 7 '18 at 10:30
1

Maybe GROUP_CONCAT would be sufficient for your purposes? Instead of giving individual columns for each member in a group, it would give a single column with comma-separated lists of members. In your case I think it would be something like:

SELECT gp.group_name as `Group Name`,
   GROUP_CONCAT(CONCAT(lc.first_name, ' ', lc.last_name)) as `Client Names`
FROM `group` gp
...
INNER JOIN client_has_group chg ON (chg.group_gr_id = gp.gr_id) 
INNER JOIN loan_client lc ON (chg.loan_client_id = lc.id)
GROUP BY gp.group_name

Which should give something like:

+----------+----------------------+
|Group Name|   Client Names       | 
+----------+----------------------+
|Group A   |   Joan,Patrick,Smith |
+----------+-------------+--------+
|Group B   |   Samuel,Elise       |
+----------+----------------------+
|Group C   |   Marco              |
+----------+----------------------+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.