How to get column values in one comma separated value

Question

I have a table which contains rows like below  ID  User      Department 1   User1     Admin 2   User1     Accounts 3   User2     Finance 4   User3     Sales 5   User3     Finance   I need a select query which results following format  ID  User      Department 1   User1     Admin Accounts 2   User2     Finance 3   User3     Sales  Finance

User · Accepted Answer

You tagged the question with both sql-server and plsql so I will provide answers for both SQL Server and Oracle.

In SQL Server you can use FOR XML PATH to concatenate multiple rows together:

select distinct t.[user],
  STUFF((SELECT distinct ', ' + t1.department
         from yourtable t1
         where t.[user] = t1.[user]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,2,'') department
from yourtable t;

See SQL Fiddle with Demo.

In Oracle 11g+ you can use LISTAGG:

select "User",
  listagg(department, ',') within group (order by "User") as departments
from yourtable
group by "User"

See SQL Fiddle with Demo

Prior to Oracle 11g, you could use the wm_concat function:

select "User",
  wm_concat(department) departments
from yourtable
group by "User"

User · Answer

Try the following Query   select distinct Users   STUFF                    select        d Department FROM  temp d         where t Users d Users group by d Department for xml path                1  2      as Departments  from  temp t   Implementation   Declare  temp Table  ID int  Users varchar 50   Department varchar 50    insert into  temp  ID Users Department  values  1  User1   Admin   insert into  temp  ID Users Department  values  2  User1   Accounts   insert into  temp  ID Users Department  values  3  User2   Finance   insert into  temp  ID Users Department  values  4  User3   Sales   insert into  temp  ID Users Department  values  5  User3   Finance     select distinct Users   STUFF                    select        d Department FROM  temp d         where t Users d Users group by d Department for xml path                1  2      as Departments   from  temp t   Result will be

User · Answer

I think it will be easy to you  I am using group concat which concatenate diffent values with separator as we have defined  select ID User  GROUP CONCAT Distinct Department order  by Department asc   separator       as Department from Table Name   group by ID

User · Answer

In Sql Server you can use it   DECLARE  UserMaster TABLE        UserID INT NOT NULL        UserName varchar 30  NOT NULL        INSERT INTO  UserMaster VALUES  1  Rakesh    INSERT INTO  UserMaster VALUES  2  Ashish    INSERT INTO  UserMaster VALUES  3  Sagar    SELECT   FROM  UserMaster  DECLARE  CSV VARCHAR MAX    SELECT  CSV   COALESCE  CSV               UserName from  UserMaster   SELECT  CSV AS Result

User · Answer

SELECT name  GROUP CONCAT  section    FROM   tmp   GROUP BY name

User · Answer

MYSQL   To get column values as one comma separated value use  GROUP CONCAT    function as    GROUP CONCAT    column name      for example  SELECT GROUP CONCAT    column name     FROM   table name   WHERE 1  LIMIT 0   30

User · Answer

You can do this with the following SQL   SELECT STUFF                 SELECT       s FirstName          FROM Employee s         ORDER BY s FirstName FOR XML PATH                 1  1       AS Employees

User · Answer

For Mysql   SELECT t user     SELECT GROUP CONCAT  t1 department   FROM table name t1 WHERE t1 user   t user department  FROM table name t GROUP BY t user LIMIT 0   30

User · Answer

For Oracle versions which does not support the WM CONCAT  the following can be used    select  User   RTRIM       XMLAGG  XMLELEMENT e  department       ORDER BY department  EXTRACT    text                  AS departments    from yourtable    group by  User    This one is much more powerful and flexible - you can specify both delimiters and sort order within each group as in listagg

[sql-server] How to get column values in one comma separated value

Examples related to sql-server