How to get the result of a query which has several lines as a result in one single line separated by one separator but having also another column?

Question

I have two tables:

Employers:
emp_id:     emp_name:
1           Smith
2           John
3           Terry
4           Sue

and antoher one,

Skills:
emp_id:  skill:
1        10
1        12
1        14
2        5
2        15

I want to get a query which returns this:

emp_id:  skill:
1        10,12,14
2        5,15

So far I'm able to get skill values as string by using:

SELECT STUFF((SELECT DISTINCT  ', ' + skill) AS [text()]
FROM employers inner join skills where emp_id=1
FOR XML PATH ('')),1,1,'')

But this does not help, because I need to join the values in my project to other columns and I need also that emp_id.

Any hints?

Answer 1

You can use the following to concatenate the values into a single row:

select e.emp_id,
  e.emp_name,
  STUFF((SELECT distinct ', ' + cast(s.skill as varchar(10))
         from Skills s
         where e.emp_id = s.emp_id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,2,'') skills
from employers e;

See SQL Fiddle with Demo

Then you can still join on the employers table.

Or you can use CROSS APPLY with FOR XML PATH:

select e.emp_id,
  e.emp_name, 
  left(s.skills, len(s.skills)-1) skills
from Employers e
cross apply
(
  select cast(s.skill as varchar(10)) + ', '
  from  Skills s
  where e.emp_id = s.emp_id
  FOR XML PATH('')
) s (skills);

See SQL Fiddle with Demo

Answer 2

Try this one -

DECLARE @Employee TABLE
(
      emp_id INT
    , emp_name NVARCHAR(50) 
)

DECLARE @Skill TABLE
(
      emp_id INT
    , skill_id INT
)

INSERT INTO @Employee (emp_id, emp_name)
VALUES 
    (1, 'Smith'),
    (2, 'John'),
    (3, 'Terry'),
    (4, 'Sue')

INSERT INTO @Skill (emp_id, skill_id)
VALUES 
    (1, 10),
    (1, 12),
    (1, 14),
    (2,  5),
    (2, 15)

SELECT 
      e.emp_id
    , skills = 
        (
            SELECT STUFF((
            SELECT DISTINCT  ', ' + CAST(s.skill_id AS VARCHAR(10))
            FROM @Skill s
            WHERE s.emp_id = e.emp_id
            FOR XML PATH(N''), TYPE, ROOT).value(N'root[1]', N'NVARCHAR(MAX)'), 1, 2, '')
        ) 
FROM @Employee e