Combine data with same id in one column

Question

I have this problem. I need to combine data with the same id in one column. The data are separated with ','. I use group_concat but the result is that it combines all. What I need is for example:

But the result is:

my code

 SELECT A.bookId,A.bookDate, A.serviceDate, A.bookTime,A.status, GROUP_CONCAT(C.serviceItemId 
 SEPARATOR ',') AS Servis, B.custId, B.custFname, B.custLname,B.custContact, B.custEmail, B.gender 
 FROM booking A 
 JOIN booking_service C on A.bookId = C.bookId 
 JOIN customer B ON B.custId = 
 A.cust_fk WHERE A.cust_fk = 4 
 ORDER BY A.bookId

Can someone help me?

Your query does not match with your sample data (the query has many more columns). Basically your query is missing a `GROUP BY` clause, but is not possible to help more without accurate sample data (which should be provided as tabular text). — GMB, Dec 09 '19 at 12:01
See: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Strawberry, Dec 09 '19 at 12:02
And, more relevantly - https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — Strawberry, Dec 09 '19 at 12:04
For your sql statement to work properly , you have first to split up the column and concat the distinct values. — nbk, Dec 09 '19 at 12:05
i solve my problem! thank you! my query is missing group by clause. thanks again!! :D — jack98, Dec 09 '19 at 12:07
Bad database design. Build a table for 1:N relation instead using a comma separated list. This make future live (queries) much easier. — Wiimm, Dec 09 '19 at 12:14
Unless you have redesigned your schema, you have *not* solved your problem. :-( — Strawberry, Dec 09 '19 at 12:19

score 0 · Answer 1 · answered Dec 09 '19 at 12:04

Why not group_concat the values in a sub-query and then join it to your main result set:

SELECT  A.bookId,
        A.bookDate, 
        A.serviceDate, 
        A.bookTime,
        A.status, 
        d.Servis,
        B.custId, 
        B.custFname, 
        B.custLname,
        B.custContact, 
        B.custEmail, 
        B.gender 
 FROM   (
        SELECT  bookId,
                GROUP_CONCAT(serviceItemId SEPARATOR ',') AS Servis
          FROM  booking_service
          GROUP BY bookId
        ) d
   JOIN booking A ON a.bookID = d.bookID
   JOIN booking_service C on A.bookId = C.bookId 
   JOIN customer B ON B.custId = A.cust_fk WHERE A.cust_fk = 4 
 ORDER BY A.bookId

You need to use GROUP BY with GROUP_CONCAT if you want to limit the results to, for example, a single bookId.

Combine data with same id in one column

my code

1 Answers1