how to get duplicate column values in comma separated

Question

I have a table like this

+----+------------+------------+---------+-----+------+------+
| id | is_deleted | sort_order | version | cid | pid  | qid  |
+----+------------+------------+---------+-----+------+------+
|  1 |            |          1 |       0 |   1 |    1 |    1 |
|  2 |            |          2 |       0 |   1 |    1 |    2 |
|  3 |            |          3 |       0 |   1 |    1 |    3 |
|  4 |            |          1 |       0 |   1 |    2 |    7 |
|  5 |            |          2 |       0 |   1 |    2 |    1 |
|  6 | ☺          |          1 |       1 |   1 |    6 |   14 |
|  7 | ☺          |          1 |       1 |   1 |    5 |   13 |
|  8 |            |          1 |       0 |   1 |    4 |   12 |
|  9 |            |          3 |       0 |   1 |    2 |    2 |
| 10 |            |          4 |       0 |   1 |    1 |    4 |
| 11 |            |          5 |       0 |   1 |    1 |    5 |
+----+------------+------------+---------+-----+------+------+

as you can see pid is repeated. Is it possible to get like below format

pid      qid
1     1,2,3,4,5
2     7,1,2
6     14
5     13
4     12

I tried like this but the output I got is

SELECT pid,GROUP_CONCAT(qid) FROM client_parent_question

------+--------------------------+
 pid  | GROUP_CONCAT(qid)        |
------+--------------------------+
    1 | 1,2,3,7,1,14,13,12,2,4,5 |
------+--------------------------+

http://sqlfiddle.com/ is not working for me in my system so guys I have not prepared fiddle for it — SpringLearner, Jun 10 '14 at 11:01
It's OK to undertake basic research into a problem before asking here. — Strawberry, Jun 10 '14 at 11:24

score 3 · Answer 1 · edited Oct 26 '18 at 16:38

3

Use GROUP BY

SELECT pid, GROUP_CONCAT(qid)
FROM client_parent_question
GROUP BY pid

edited Oct 26 '18 at 16:38

octano

851
1
10
18

answered Jun 10 '14 at 11:02

Vignesh Kumar A

27,863
13
63
115

score 2 · Accepted Answer · answered Jun 10 '14 at 11:02

2

You are missing group by

SELECT pid,GROUP_CONCAT(qid) FROM client_parent_question group by pid

answered Jun 10 '14 at 11:02

Abhik Chakraborty

44,654
6
52
63

score 1 · Answer 3 · answered Jun 10 '14 at 11:04

1

SELECT pid,GROUP_CONCAT(qid) FROM client_parent_question
Group by PID

would do the trick. Output would be as:

pid   |   qid
1     |   1,2,3,4,5
2     |   7,1,2
6     |   14
5     |   13
4     |   12

answered Jun 10 '14 at 11:04

ngrashia

9,869
5
43
58

how to get duplicate column values in comma separated

3 Answers3