Concat field value to string in SQL Server

I need a similar function to Oracle WM_CONCAT in SQL Server, which returns a comma separated list of whatever field you pass it as argument. For example, in Oracle,

select WM_CONCAT(first_name) from employee where state='CA' 

returns "John, Jim, Bob".

How can I do this in SQL Server?

Thanks

Solution 1:

In SQL Server 2017 STRING_AGG function has been added

SELECT t.name as TableName
      ,STRING_AGG(c.name, ';') AS FieldList
  FROM sys.tables t
  JOIN sys.columns c 
    ON t.object_id = c.object_id
  GROUP BY t.name;

Solution 2:

The actual answer:

SELECT
   SUBSTRING(buzz, 2, 2000000000)
FROM
    (
    SELECT 
        firstname
    FROM 
        employee
    WHERE
        State = 'CA'
    FOR XML PATH (',')
    ) fizz(buzz)

A common question here. Some searches:

• FOR XML PATH

• concat rows csv [sql-server]

Solution 3:

Try this:

    drop table #mike_temp 
go

select * into #mike_temp 
  from (select 'Ken'  as firstname, 'CO' as state
         union all
        select 'Mike' as firstname, 'CO' as state
         union all
        select 'Tom' as firstname , 'WY' as state
       ) a
go

SELECT distinct 
       state
      ,STUFF((SELECT ', ' + b.firstname FROM #mike_temp b where a.state = b.state FOR XML PATH('')),1, 2, '') AS CSVColumn
  from #mike_temp a