SELECT with derived csv value included

Question

I want to have a SELECT that as part of list of return fields would also include a csv of multiple values from another table.

I have code working to determine the CSV from here: SQL Server 2008 Rows to 1 CSV field

But i am not able to get a working JOIN with the csv value, or get at the id field of the derived csv values.

My two tables are:

job (id,title,location)
skills(id, jobID, skillName, listOrder)

I want to do a select * from job with each job record having its own derived skills

basically result should be: 1,dba,texas, [sql,xml,rdp] <-- skills for jobid=1

possible duplicate of [Is there a way to create a SQL Server function to "join" multiple rows from a subquery into a single delimited field?](http://stackoverflow.com/questions/6899/is-there-a-way-to-create-a-sql-server-function-to-join-multiple-rows-from-a-su) — OzrenTkalcecKrznaric, Jul 08 '15 at 20:43

score 1 · Accepted Answer · answered Jul 08 '15 at 20:48

This seems to do, what you are looking for:

SELECT
  J.id
  , J.title
  , J.location
  , '[' + STUFF ((SELECT ',' + S.skillName
                  FROM Skills S
                  WHERE
                    J.id = S.jobID
                  ORDER BY S.listOrder
                  FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)')
                , 1, 1, '') + ']' skillSet
FROM Job J
GROUP BY J.id, J.title, J.location
;

See it in action: SQL Fiddle

Please comment, if and as this requires adjustment / further detail.

SELECT with derived csv value included

1 Answers1