SQL query to get combine multiple id in single value with comma separators

Question

I have 2 tables Employee and Skills with following fields:

Employee:Id,Name,Location

Skills:Id,Skills

Employee data:

Id    Name   Location
7     Abc     London
8     Xyz     London
9     Xyz     USA
10    Abc     USA

Skills:

Id   skills     EmployeeId
12    AAA Xyz     7
13    BBB         7
14    CCC         7
15    AAA         8
16    BBB         8
17    CCC         8
18    AAA         9
19    BBB         9
20    CCC         9
21    AAA         10
22    BBB         10
20    CCC         10

Now I want to get skills of those employee whose Name is Abc,Location is London and Skills is both AAA and BBB only but I want single output like below:

Skills
10,11

This is my query:

select S.Id from Employee E inner join
Skills S on E.Id=s.EmployeeId
where (E.Name='Abc' and E.Location='London'
and s.skills='AAA') and (E.Name='Abc' and E.Location='London'
and s.skills='BBB')

But I am getting wrong output.

Possible duplicate of [How can I combine multiple rows into a comma-delimited list in SQL Server 2005?](http://stackoverflow.com/questions/180032/how-can-i-combine-multiple-rows-into-a-comma-delimited-list-in-sql-server-2005) — JamieD77, Jun 24 '16 at 14:21

Siyual · Accepted Answer · 2016-06-24T14:55:25.323

You can use STUFF for this:

Select  Stuff
(
    (
        Select  ',' + Convert(Varchar, S.Id)
        From    Employee    E
        Join    Skills      S   On  S.EmployeeId = E.Id
        Where   E.Name = 'Abc'
        And     E.Location = 'London'
        And     S.Skills In ('AAA', 'BBB')
        For Xml Path ('')
    ), 1, 1, ''
) As Skills

Edit: SQL Fiddle appears to not want to work with me at the moment, but using the following data (from OP), it does get the correct results:

Create Table Skills
(
  Id int,
  skills varchar (3),
  employeeid int
);

Create Table Employee
(
  Id int,
  Name varchar (10),
  location varchar (10)
  );

insert skills values 
(10    ,'AAA',      1),
(11    ,'BBB',      1),
(13    ,'CCC',      1),
(14    ,'DDD',      1);

insert employee values
(    1     ,'Abc' ,    'London'),
(    2     ,'Xyz',     'Uk');


Select  Stuff
(
    (
        Select  ',' + Convert(Varchar, S.Id)
        From    Employee    E
        Join    Skills      S   On  S.EmployeeId = E.Id
        Where   E.Name = 'Abc'
        And     E.Location = 'London'
        And     S.Skills In ('AAA', 'BBB')
        For Xml Path ('')
    ), 1, 1, ''
) As Skills

Edit #2 New data; same query:

Create Table Skills
(
  Id int,
  skills varchar (3),
  employeeid int
);

Create Table Employee
(
  Id int,
  Name varchar (10),
  location varchar (10)
  );

insert skills values
(12    ,'AAA',      7   ),
(13    ,'BBB',      7   ),
(14    ,'CCC',      7   ),
(15    ,'AAA',      8   ),
(16    ,'BBB',      8   ),
(17    ,'CCC',      8   ),
(18    ,'AAA',      9   ),
(19    ,'BBB',      9   ),
(20    ,'CCC',      9   ),
(21    ,'AAA',      10  ),
(22    ,'BBB',      10  ),
(20    ,'CCC',      10  );

insert employee values
(7     ,'Abc',     'London'  ),
(8     ,'Xyz',     'London'  ),
(9     ,'Xyz',     'USA'       ),
(10    ,'Abc',     'USA'       );


Select  Stuff
(
    (
        Select  ',' + Convert(Varchar, S.Id)
        From    Employee    E
        Join    Skills      S   On  S.EmployeeId = E.Id
        Where   E.Name = 'Abc'
        And     E.Location = 'London'
        And     S.Skills In ('AAA', 'BBB')
        For Xml Path ('')
    ), 1, 1, ''
) As Skills

The results this time are 12,13 - which is correct based on your conditions:

Employee 7 is the only one with Abc from London, and 12 and 13 are the AAA and BBB skills.

How is it wrong? What is it displaying? I need a little more to go on... — Siyual, Jun 24 '16 at 14:31
I'd post a SQL Fiddle, but it doesn't want to work with me at the moment. But using your sample data, I get the *exact* result you're asking for. — Siyual, Jun 24 '16 at 14:39
I dont know why this query is giving me unexpected output.You think if i have Location like this:New jersey and instead of london i am searching for this New jersey then this could be problem with space in my location field? — I Love Stackoverflow, Jun 24 '16 at 14:59
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/115536/discussion-between-learning-and-siyual). — I Love Stackoverflow, Jun 24 '16 at 15:01
I'm not sure - please analyze the output you're getting. What do those IDs map to? Look at the data you have - does it make sense with those IDs? — Siyual, Jun 24 '16 at 15:01
Sorry i have not check properly with my records and so i was getting output like that.Your query is working perfect.Thank you so much — I Love Stackoverflow, Jun 27 '16 at 05:26

SQL query to get combine multiple id in single value with comma separators

1 Answers1