Return multiple values in one column

Question

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

1. What is your RDBMS? 2. What exactly is your question? 3. Show us your attempt before you expect us to help you! — Radu Gheorghiu, Jun 07 '13 at 07:17
possible duplicate of [SQL - Combining results (rows) of one value](http://stackoverflow.com/questions/12291564/sql-combining-results-rows-of-one-value) — Martin Smith, Jun 07 '13 at 07:23
+1 Good question, apologies for the downvotes. You can find a solution for this by searching for "group concat ", [example search here](https://www.google.com/search?q=sqlserver+group+concat) — Andomar, Jun 07 '13 at 07:27
@Andomar - I'm not sure on which alternate reality this would be considered a good question. Variants of it are asked daily so it shows no research effort. Also omits the rather crucial information that they are using HSQLDB. — Martin Smith, Jun 07 '13 at 11:12
@MartinSmith: Many people benefit from asking and answering this question. To regulars, this might not be very interesting, but regulars are not SO's only constituency. As to why you can't Google this question, that's because you don't know what to Google for. I've seen several people try, IRL, and they're intelligent and smart, but they don't figure out that "group concat" is the magic word. — Andomar, Jun 07 '13 at 11:25
@Andomar - If you google the question title "Return multiple values in one column" are you telling me you don't see relevant results? — Martin Smith, Jun 07 '13 at 11:27
@MartinSmith: It doesn't make sense to me either. But I know for sure well-meaning people can't figure it out by themselves. — Andomar, Jun 07 '13 at 11:41

peterm · Answer 1 · 2013-06-08T06:23:53.190

5

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) |

edited Jun 08 '13 at 06:23

answered Jun 07 '13 at 07:31

peterm

91,357
15
148
157

HI Peterm thnks for the answers.. and sorry that i dint tell my RDBMS.. I am using HSQLDB – Shivangi Gupta Jun 07 '13 at 09:06
@ShivangiGupta - Looks like `GROUP_CONCAT` is available for [versions >= 2](http://stackoverflow.com/questions/16978291/return-multiple-values-in-one-column#comment24532063_16978291) – Martin Smith Jun 07 '13 at 11:19
@ShivangiGupta Just use MySql version of the query and let me know if it helped. It works just fine on HSQLDB 2.2. – peterm Jun 08 '13 at 06:25
@Peterm- I tried MySql but it is not working for me.It is showing this error Routine (group_concat) can not be resolved. – Shivangi Gupta Jun 10 '13 at 05:59
@ShivangiGupta What version of HSQLDB are you on? – peterm Jun 10 '13 at 06:00
@Peterm:- I am using the old version 1.x, i think for this lower version GROUP_CONCAT will not work. – Shivangi Gupta Jun 10 '13 at 10:37
@ShivangiGupta No, it won't. You might consider to upgrade to 2.2.9. – peterm Jun 11 '13 at 04:39

score 0 · Answer 2 · answered Jun 07 '13 at 07:30

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

Return multiple values in one column

2 Answers2

Linked

Related