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

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

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

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

SELECT name  GROUP CONCAT  section    FROM   tmp   GROUP BY name

User · 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

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

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

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

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

Examples related to sql-server