SQL group concat with duplicate ID's-CodePudding

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?

CodePudding user response：

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 |

Edit

I saw your comment & edited the question, if you are using amazon-redshift you can try to use LISTAGG instead GROUP_CONCAT