displaying multiple rows in single row in sql query

Question

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 1

I tried the above code but it is not working for me in MySQL.

    SELECT  e.emp_id, GROUP_CONCAT( DISTINCT t.`tech_name` ORDER BY `et`.`technology_id` ASC SEPARATOR '/' )  employee_name FROM employee e, employeeTechnology et, technology t  WHERE e.emp_id = et.emp_id AND et.technology_id = t.technology_id  GROUP BY `e`.`emp_id` 

Now I tried its working fine. I have a question that doesn't MySQL support STUFF function?

Answer 2

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