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

Which RDBMS is this for? Please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. — marc_s
– marc_s, Commented Jul 26, 2016 at 7:09
@J. Chomel:i have tried this,, 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 — venkat
– venkat, Commented Jul 26, 2016 at 7:17
Not to me! I know nothing to sql-server, I only know Oracle. You can edit your question :) — J. Chomel
– J. Chomel, Commented Jul 26, 2016 at 7:18

IUnknown · Accepted Answer · 2016-07-26 07:48:15Z

1

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

answered Jul 26, 2016 at 7:48

IUnknown

22.5k4 gold badges41 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Josphine Priyanka · Accepted Answer · 2016-07-27 04:38:08Z

0

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?

answered Jul 27, 2016 at 4:38

Josphine Priyanka

32 bronze badges

Collectives™ on Stack Overflow

displaying multiple rows in single row in sql query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related