CODEWITHSUNDEEP
sql
Shivangi Gupta
Shivangi Gupta

Reputation: 49

Return multiple values in one column

I have two tables

Employee:

Empid Ename Eage Eadd Ephone
1      x     23   b    677
2      y     24   h    809 
3      z     34   u    799

Department:

Did  fkEmpid dname ddescription
123    1     test   test
234    1     test1  test1
667    2     hello  hello

Finally I want something like this

Ename Eage  Eadd Ephone  dname
x      23     b   677     test,test1
y      24     h   809     hello
z      34     u   799     null

Please help me with the SQL

Upvotes: 2

Views: 214

Answers (2)

peterm
peterm

Reputation: 92845

It certainly would be nice to know the target RDBMS. But this question is asked so often so let's try and list'em all (at least popular ones) side by side.

For SQL Server:

SELECT e.Ename, e.Eage, e.Eadd, e.Ephone, d.dname
  FROM Employee e LEFT JOIN 
(
  SELECT fkEmpid,
         STUFF((SELECT ',' + dname 
                    FROM Department 
                   WHERE fkEmpid = t.fkEmpid 
                     FOR XML PATH('')) , 1 , 1 , '' ) dname
    FROM Department t
   GROUP BY fkEmpid
) d
    ON e.Empid = d.fkEmpid

Here is SQLFiddle demo

For Mysql, SQLite, HSQLDB 2.X:

SELECT e.Ename, e.Eage, e.Eadd, e.Ephone, d.dname
  FROM Employee e LEFT JOIN 
(
  SELECT fkEmpid,
         GROUP_CONCAT(dname) dname
    FROM Department t
   GROUP BY fkEmpid
) d
    ON e.Empid = d.fkEmpid

Here is SQLFiddle demo (MySql)
Here is SQLFiddle demo (SQLite)

For Oracle 11g:

SELECT e.Ename, e.Eage, e.Eadd, e.Ephone, d.dname
  FROM Employee e LEFT JOIN 
(
  SELECT fkEmpid,
         LISTAGG (dname, ',') WITHIN GROUP (ORDER BY dname) dname
    FROM Department t
   GROUP BY fkEmpid
) d
    ON e.Empid = d.fkEmpid

Here is SQLFiddle demo

For PostgreSQL 9.X:

SELECT e.Ename, e.Eage, e.Eadd, e.Ephone, d.dname
  FROM Employee e LEFT JOIN 
(
  SELECT fkEmpid,
         string_agg(dname, ',') dname
    FROM Department t
   GROUP BY fkEmpid
) d
    ON e.Empid = d.fkEmpid

Here is SQLFiddle demo

Output in all cases:

| ENAME | EAGE | EADD | EPHONE |      DNAME |
---------------------------------------------
|     x |   23 |    b |    677 | test,test1 |
|     y |   24 |    h |    809 |      hello |
|     z |   34 |    u |    799 |     (null) |

Upvotes: 5

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11609

Considering RDBMS as SQL SERVER 2008

select E.Ename,E.Eage,E.Eadd,E.Ephone,D.dname
into Table1
from Employee E
left join Deparment D on E.Empid=D.fkEmpid

select t1.[Ename], t1.[Eage], t1.[Eadd], t1.[Ephone],
STUFF((
    SELECT ', ' + t2.dname
    FROM Table1 t2
    WHERE t2.Ename = t1.Ename
      AND t2.Eage=t1.Eage
      AND t2.Eadd=t1.Eadd
      AND t2.Ephone=t1.Ephone
    FOR XML PATH (''))
  ,1,2,'') AS Names
FROM Table1 t1
GROUP BY t1.Ename,t1.[Eage], t1.[Eadd], t1.[Ephone];

SQL FIDDLE

Upvotes: 0

Related Questions