Get All items Group by a specific column values [duplicate]

Question

I have a table as below in MYSQL:

cname | vname | curl --------------------- A | 1 | url1 A | 2 | url2 B | 1 | url3 B | 3 | url4 C | 2 | url5 C | 3 | url5 C | 4 | url6 D | 2 | url7

And I want to show the result as under:

1 | 2 | 3 | 4 ------------------- A | A | B | C B | C | C | | D |

In short, I am trying to show all the cnames group by vnames.

I have tried the following code in Codeigniter:

$this->db->distinct(); $this->db->select('vname, cname, curl'); $this->db->from('tablename'); $this->db->order_by('cname'); $this->db->group_by('vname'); $res = $this->db->get(); if($res->num_rows()>0) var_dump($res->result());

I am getting only one row per vname as a result of var_dump();

Plesae provide a solution for this problem.

Do not try to do this in sql, producing this output is very inefficient. Do the transformation in the php code — Shadow
– Shadow, Commented Apr 27, 2018 at 5:10
Also, to accommodate an arbitrary (unknown) number of vname value columns, you'd nee dynamic SQL, which is unwieldy. — Tim Biegeleisen
– Tim Biegeleisen, Commented Apr 27, 2018 at 5:11
@Shadow Bro I wrote above that I am doing it in Codeigniter (a PHP framework) and I have also shared the code. — ITSagar
– ITSagar, Commented Apr 27, 2018 at 5:12
Bro, your php code only generates an sql query and displays its result. You cannot do it that way. — Shadow
– Shadow, Commented Apr 27, 2018 at 5:15
@IT Sagar :Please this code:- $this->db->select('DISTINCT(vname), cname, curl'); $this->db->group_by('vname'); $query = $this->db->get('tablename'); $res = $this->db->get(); if($res->num_rows()>0) var_dump($res->result()); — sunny bhadania
– sunny bhadania, Commented Apr 27, 2018 at 5:29

R B · Accepted Answer · 2018-04-27 05:42:26Z

Use GROUP_CONCAT

$this->db->select('vname,GROUP_CONCAT(cname SEPARATOR ",") as cname'); $this->db->from('tablename'); $this->db->group_by('vname'); $res = $this->db->get();

Output :

+--------+--------+ | vname | cname | +--------+--------+ | 1 | A,B | | 2 | A,C,D | | 3 | B,C | | 4 | C | +--------+--------+

It is a trivial operation to transpose the result set for presentation in the view.

Collectives™ on Stack Overflow

Get All items Group by a specific column values [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related