Join table column into array and group by-CodePudding

I have the following tables:

JOBS TABLE
job_id     description
1          test
2          asdf

JOB_COSTCODES TABLE
job_id     cost_code_id
1          5
1          8
2          5
2          9
2          7

My goal is to select all of the jobs once, with all of the associated cost_code_ids as a comma separated string column.

job_id         description     cost_code_ids
1              test            5,8
2              asdf            5,9,7

I have the following SQL:

SELECT
    jobs.job_id, jobs.description,
        concat(',', job_costcodes.cost_code_id)
FROM
    jobs
JOIN
    job_costcodes ON job_costcodes.job_id = jobs.job_id

However, this returns the same job split into multiple rows with only a single cost_code_id in each row. I tried adding GROUP BY jobs.job_id at the end of the query, but that results in the error:

Column 'jobs.description' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

CodePudding user response：

STUFF and FOR XML can do the trick:

SELECT job_id, 
       description, 
       STUFF((
        SELECT ','   cost_code_id
         FROM job_costcodes cc
         WHERE cc.job_id = j.job_id
         FOR XML PATH('')
        ), 1, 1, '') as cost_code_ids 
FROM JOBS j

Other ways to concatenate multiple rows into a single string here