help writing a SQL query to join a table on itself

Question

I have a SQL query that is returning a table like the following:

id  emp_name    total_explabor_grade    title       name
518 Name,One    3   PE4 Software Engineer   Java
492 Name,Two    4   PE1 Software Engineer   Java
502 Name,Three  1   SPE5    Principal       Javascript
410 Name,Four   3   ENG3    Software Engineer   Java
147 Name,Five   5   SPE5    Director        Java
147 Name,Five   2   SPE5    Director        Javascript
156 Name,Six    10  PE2 Senior          Java
156 Name,Six    8   PE2 Senior          Javascript

This was based off a query for people that have the skills of Java OR Javascript. As you can see, employee 147 and 156 had a hit for both Java and Javascript.

If I am storing this current query into a temporary table, say #TempTable

What query can I run on #TempTable to give me a result like this:

id  emp_name    total_explabor_grade    title       name
518 Name,One    3   PE4 Software Engineer   Java
492 Name,Two    4   PE1 Software Engineer   Java
502 Name,Three  1   SPE5    Principal       Javascript
410 Name,Four   3   ENG3    Software Engineer   Java
147 Name,Five   5,2 SPE5    Director        Java,Javascript
156 Name,Six    10,8    PE2 Senior          Java,JavaScript

I have tried to do a Join of #TempTable with itself in various forms, but I havent been able to get it to give me a table like what I just posted. This is the code I have written so far...

select t1.id, t1.emp_name, CONVERT(nvarchar(3),t1.total_exp)+','+CONVERT(nvarchar(3),t2.total_exp), t1.labor_grade, t1.title, t1.name+','+t2.name AS hits
from #TempTable t1 JOIN #TempTable t2
 ON t1.id=t2.id

Does anyone know if it is possible to do a join like I am looking to do? My latest line of thinking is it will take more than one select, but I havent quite figured it out

possible duplicate of [SQL Server: Can I Comma Delimit Multiple Rows Into One Column?](http://stackoverflow.com/questions/2046037/sql-server-can-i-comma-delimit-multiple-rows-into-one-column) — OMG Ponies, Dec 17 '10 at 18:13

score 2 · Accepted Answer · answered Dec 17 '10 at 18:13

2

Maybe you can come up with a solution by group_concat

Group on the equal columns making the identity of employee and group_concat on others that you want to be concatenated.

answered Dec 17 '10 at 18:13

Jahan Zinedine

14,616
5
46
70

I was able to get something sort of working with this. It works for one column, but I can't get it to work for the 2nd column at the same time. +1 for pointing in the right direction though – Derek Dec 17 '10 at 19:43

score 0 · Answer 2 · answered Dec 17 '10 at 18:12

This is not possible with a self-join, based on your data, because your rows don't have a unique key. For a self-join to be an option, you'd have to prevent a row from being joined with itself. Because of this, your query above would actually return 2 rows for id 147.

A simple way to go about this is would be a query that selects distinct ids and names from temptable, and then use 4 functions that return a concatenated string of all the occurences of years, labor grade, title, and skills for an id.

score 0 · Answer 3 · answered Dec 17 '10 at 18:31

This is not a self join task because you would have to join the table in one time for each possible skill. If the maximum number of possible skills for an employee is known in advance and is reasonably small you might begin to approach it with a self join but that's unlikely to be true. (On the flip side, if the number of skills is really guaranteed to be only Java and Javascript and nothing else, you can do it with a self-join or even a simpler MIN/MAX GROUP BY).

This is actually an aggregation issue to be solved with a GROUP BY query. Unfortunately, SQL Server does not have a string concatenation aggregate function built in (as does MySQL and some other products). You can approximate it in a couple of different ways (CTE or For XML), other respondents have linked articles that demonstrate those techniques.

help writing a SQL query to join a table on itself

3 Answers3