MySQL get multiple rows into columns

Question

I have a table called visits where concat(s_id, c_id) is unique and id is the primary key. s_id is the ID number of a website and c_id is a campaign ID number. I want to show all the hits each campaign is getting and group by the site. I want each site on a single row

  +-----+------+------+------+
  | id  | s_id | c_id | hits | 
  +-----+------+------+------+
  |  1  |   13 |   8  |  245 |
  |  2  |   13 |   8  |  458 |
  |  3  |   13 |   3  |   27 |
  |  4  |   13 |   4  |  193 |
  |  5  |   14 |   1  |  320 |
  |  6  |   14 |   1  |  183 |
  |  7  |   14 |   3  |  783 |
  |  8  |   14 |   4  |  226 |
  |  9  |   5  |   8  |  671 |
  |  10 |   5  |   8  |  914 |
  |  11 |   5  |   3  |  548 |
  |  12 |   5  |   4  |  832 |
  |  13 |   22 |   8  |   84 |
  |  14 |   22 |   1  |   7  |
  |  15 |   22 |   3  |  796 |
  |  16 |   22 |   4  |   0  |
  +----+------+------+-------+

I would like to have the following result set:

s_id | hits | hits | hits| hits
 13  | 245  | 458  | 27  | 193
 14  | 320  | 183  | 783 | 226
 5   | 671  | 914  | 548 | 832
 22  | 84   | 7    | 796 | 0

Here is what I have tried which does not pull all the hits columns back.

SELECT v.*, v2.* FROM visits v
INNER JOIN visits v2 on v.s_id = v2.s_id
GROUP BY s_id

How can I get multiple rows into columns?

Shujaat · Accepted Answer · 2015-11-17 21:32:02Z

3

If your'e data set is not crazy huge and you are just trying to get the multiple rows as a single row.... one way to do this...

SELECT
s_id, 
GROUP_CONCAT(hits SEPARATOR ',') as hits_list 
FROM
visits
GROUP BY s_id

Since it doesn't use any joins or subqueries etc, i find this way to be quite fast.

you can later split/explode the data based on the ',' separator in PHP or whatever language you are using.

$hits = explode($hits_list, ','); //get them in an array

answered Nov 17, 2015 at 21:32

Shujaat

6915 silver badges18 bronze badges

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

5 Comments

Sarath Chandra Over a year ago

A straightforward and simple solution. However, data about website hits is more likely to be 'crazy huge' if I am not wrong. I'd love to see a more performance-efficient approach of doing this.

Shujaat Over a year ago

usually if the data is big, the group_concat_max_len can be modified. SET SESSION group_concat_max_len = 1000000;

Sarath Chandra Over a year ago

I completely agree. But are you aware of a way which doesn't use GROUP BY at all... because that would be totally kickass...!!!

Shujaat Over a year ago

Not that i am aware of :(

Brént Russęll Over a year ago

In this case I am actually going to join this query to 3 other tables however for simplicity of my question, I keep it out. This will be in the admin interface so it will not be hit too hard but it is agreed that on high traffic, this would be a little more difficult.

Collectives™ on Stack Overflow

MySQL get multiple rows into columns

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related