Table to view with group_concat

Question

I have the following table and i want to generate a view. See the result. How can i achieve that? I tried with group_cat but that doesnt work :(

id product_id cat_id date
1  1          1      2018-05-01
2  1          1      2018-05-02
3  1          1      2018-05-04
4  1          1      2018-05-05
5  1          1      2018-05-06
6  1          1      2018-05-07
4  1          1      2018-05-08
5  1          1      2018-05-09
6  1          1      2018-05-10
7  1          2      2018-05-01
8  1          2      2018-05-02
9  1          2      2018-05-04
10  1          2      2018-05-05
11  1          2      2018-05-06
12  1          2      2018-05-07
13  1          2      2018-05-08
14  1          2      2018-05-09
15  1          2      2018-05-10

Result:

product_id cat_id dates
1          1      2018-05-01,2018-05-02,2018-05-03,2018-05-04,etc comma seperated
1          2      2018-05-01,2018-05-02,2018-05-03,2018-05-04,etc comma seperated

Query:

select
  tmp.product_id, tmp.cat_id, group_concat(tmp.date separator ',') as dates
from xxxx as tmp
group by tmp.cat_id;

Edit your question and show what you have tried. Also, explain what you mean by "doesn't work". — Sloan Thrasher, Mar 12 '18 at 14:36
You hav error .. using group_concat ..? . (not group_cat) show us your code and eventually your error — ScaisEdge, Mar 12 '18 at 14:38
@MKhalidJunaid that did the trick :) Please give it as awnser! — Bas, Mar 12 '18 at 14:41

score 2 · Accepted Answer · answered Mar 12 '18 at 14:42

2

Your group by should be tmp.product_id, tmp.cat_id

select
  tmp.product_id, tmp.cat_id, group_concat(tmp.date separator ',') as dates
from demo as tmp
group by tmp.product_id, tmp.cat_id

Demo

answered Mar 12 '18 at 14:42

M Khalid Junaid

63,861
10
90
118

1

Thank you for the quick answer – Bas Mar 12 '18 at 14:43
is it also possible to do a group by on dates, i am getting an error. – Bas Mar 12 '18 at 15:20
@Bas can you show complete query in which you are trying a group by clause may be its better to ask a new one – M Khalid Junaid Mar 12 '18 at 17:41

score 1 · Answer 2 · answered Mar 12 '18 at 14:44

1

Try this:

SELECT product_id, cat_id, GROUP_CONCAT(date SEPARATOR ',') dates FROM `table_name` GROUP BY product_id, cat_id

answered Mar 12 '18 at 14:44

Manash Kumar

995
6
13

Why are you wasting effort writing the same thing as in the first answer. Just cast an up vote on it. – cdaiga Mar 12 '18 at 15:16
1

Solution same, but almost same time. Did not notice that when I had started writing – Manash Kumar Mar 12 '18 at 15:18
I see I vote up! – cdaiga Mar 12 '18 at 15:25

Table to view with group_concat

2 Answers2