SQL group concat with duplicate ID's

March 25, 2022

I am trying to concat the week_num from a table, grouping bby Driver_id. But group_concat will not keep duplicate ID’s, but My requirement is to have them.
Is there any way to achieve this?

>Solution :

You can try to use subquery & self-join

Query 1:

SELECT t2.*,t1.Consolidate_weeks
FROM (
    SELECT Driver_id,GROUP_CONCAT(Week_num) Consolidate_weeks
    FROM T
    GROUP BY Driver_id
) t1 INNER JOIN T t2
ON t1.Driver_id = t2.Driver_id

Results:

| Driver_id | Week_num | Consolidate_weeks |
|-----------|----------|-------------------|
|         1 |   Week 1 |     Week 1,Week 2 |
|         2 |   Week 1 |     Week 1,Week 2 |
|         3 |   Week 1 |     Week 1,Week 2 |
|         4 |   Week 1 |     Week 1,Week 2 |
|         1 |   Week 2 |     Week 1,Week 2 |
|         2 |   Week 2 |     Week 1,Week 2 |
|         3 |   Week 2 |     Week 1,Week 2 |
|         4 |   Week 2 |     Week 1,Week 2 |