Creating a List within a Field Using CAST()

Question

I am trying to create a column titled "skills" which lists each skill a given person is associated with. Essentially, I am currently receiving the following results:

NAME            SKILL

Person A        Programming
Person A        Web Design
Person A        SQL
Person B        Project Management
Person B        Written Communication

And I need to get results like this:

NAME            SKILL

Person A        Programming, Web Design, SQL
Person B        Project Management, Written Communication

This is what my SQL code currently looks like:

CAST((
    SELECT InterestCodeRoot.Code + ','
    FROM InterestCodeRoot
    WHERE EmployeeInterestCode.CodeIdent = InterestCodeRoot.CodeIdent
    FOR XML PATH(''))as varchar(max))
    AS [Skill ID],

I have also tried using STUFF() and GROUP_CONCAT() but neither alternative has worked. This is the closest I have gotten. Any advice or assistance would be appreciated.

Does this answer your question? [How do I create a comma-separated list using a SQL query?](https://stackoverflow.com/questions/1817985/how-do-i-create-a-comma-separated-list-using-a-sql-query) — Thom A, Jul 01 '21 at 15:56
Does this answer your question? [Comma separated results in SQL](https://stackoverflow.com/questions/18870326/comma-separated-results-in-sql) — Thom A, Jul 01 '21 at 15:56
Your query doesn't match your tables column names. Not sure what is the question about. — Serg, Jul 01 '21 at 16:03
Does this answer your question? [Comma separated results in SQL](https://stackoverflow.com/questions/18870326/comma-separated-results-in-sql) — Charlieface, Jul 01 '21 at 22:01

score 0 · Accepted Answer · answered Jul 01 '21 at 15:56

0

The simplest method is string_agg(), available in the more recent versions of SQL Server:

select name, string_agg(skill, ', ') within group (order by skill)
from InterestCodeRoot
group by name;

answered Jul 01 '21 at 15:56

Gordon Linoff

1,242,037
58
646
786

Creating a List within a Field Using CAST()

1 Answers1