venkat venkat - 4 months ago 9
SQL Question

displaying multiple rows in single row in sql query

Below is my table, can you please refer
i am using SQL

employee table:

emp_id(primary key) - emp_name - emp_mobile
1 raju 123456899
2 hari 454654562
3 aravindth 545485454
4 siva 549855291


employeeTechnology table:

emptech_id(primary key) - emp_id(foreign key) - technology_id(foreign key)
1 1 1
2 1 2
2 1 5
3 2 1
4 2 3
5 3 4
6 3 5
7 4 3
8 4 4


technology table:

technology_id(primary key) - tech_name
1 java-j2ee
2 Dot.net
3 DBA-SQL
4 big-data
5 SAP


I want to output like this:

emp_id - tech_name
1 java-j2ee/Dot.net/SAP
2 java-j2ee/DBA-SQL
3 big-data/SAP
4 DBA-SQL/big-data


I have already tried the following:
select e.emp_id,t.technology_id from employee e,employeeTechnology et,technology t where e.emp_id =et.emp_id and et.technology_id = t.technology_id

Answer

You should use STUFF function

SELECT
    e.emp_id, STUFF((SELECT '/' + t.tech_name 
     FROM
        dbo.employeeTechnology et 
     INNER JOIN
        dbo.technology t
     ON et.technology_id = t.technology_id
     WHERE
        et.emp_id = e.emp_id
     FOR XML PATH('')), 1, 1, '')
FROM
    employee e