Concentrate two rows in mysql, but keep column names

user_13152166 0
Viewed 50

Mysql (v. 5.1) db of domain and the number of Facebook shares.

tblFacebook

+------------+--------+----------+
| day        | shares | domainid |
+------------+--------+----------+
| 1571011200 |    441 |        1 |
| 1571097600 |    443 |        1 |
| 1571184000 |    474 |        1 |
| 1571270400 |    518 |        1 |
| 1571184000 |    849 |        3 |
| 1571270400 |    849 |        3 |
+------------+--------+----------+

I'd like to see the following output:

+------------+------------------+------------------+
| day        | domainid1-shares | domainid3-shares |
+------------+------------------+------------------+
| 1571011200 |    441          |                  |
| 1571097600 |    443          |                  |
| 1571184000 |    474          |  849             |
| 1571270400 |    518          |  849             |
+------------+-----------------+------------------+

How?

I did try this, but it did not give me the two separate columns (domainid1-shares and domainid3-shares)?

SELECT day, GROUP_CONCAT(shares SEPARATOR ', ') FROM tblFacebook GROUP BY day;
pivot group-by mysql sql
1 Answers

You can do pivot with conditional aggregation:

select 
    day,
    max(case when domainid = 1 then shares end) domainid1_shares,
    max(case when domainid = 3 then shares end) domainid3_shares
from mytable t
group by day

Demo on DB Fiddle:

       day | domainid1_shares | domainid3_shares
---------: | ---------------: | ---------------:
1571011200 |              441 |             null
1571097600 |              443 |             null
1571184000 |              474 |              849
1571270400 |              518 |              849
user_106767160
Related