Combine Multiple Fields into One Text Field in SQL Server

Question

So I have a table that looks like this:

Name                 ID           TaskID           HoursAssigned
----------------------------------------------------------------
John Smith           4592         A01              40
Matthew Jones        2863         A01              20
Jake Adams           1182         A01              100
Matthew Jones        2863         A02              50
Jake Adams           2863         A02              10

and I want to return a data set that looks like this:

TaskID               PeopleAssigned
------------------------------------------------------------
A01                  Jake Adams, John Smith, Matthew Jones
A02                  Matthew Jones, Jake Adams

The problem here is that I would have no idea how many people are assigned to a given task. Any suggestions would be great!

Answer 1

This question is asked daily here. Here is yesterday's. And another one

https://stackoverflow.com/questions/tagged/sql+string-concatenation

Answer 2

TRY THIS:

declare @table table (name varchar(30), ID int, TaskID char(3), HoursAssigned int)

insert into @table values ('John Smith'   ,4592 ,'A01'  ,40)
insert into @table values ('Matthew Jones',2863 ,'A01'  ,20)
insert into @table values ('Jake Adams'   ,1182 ,'A01'  ,100)
insert into @table values ('Matthew Jones',2863 ,'A02'  ,50)
insert into @table values ('Jake Adams'   ,2863 ,'A02'  ,10)

--formatted so you can see what is happening
SELECT DISTINCT
    t1.TaskID
       ,SUBSTRING(
                  replace(
                          replace(
                                  (SELECT
                                       t2.Name
                                       FROM @Table AS t2
                                       WHERE t1.TaskID=t2.TaskID
                                       ORDER BY t2.Name
                                       FOR XML PATH(''))
                                 ,'</NAME>','')
                         ,'<NAME>',', ')
                 ,3,2000)  AS PeopleAssigned
    FROM @table AS t1

BASED ON YESTERDAY'S ANSWER!

here is the output:

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
TaskID PeopleAssigned
------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A01    Jake Adams, John Smith, Matthew Jones
A02    Jake Adams, Matthew Jones

(2 row(s) affected)

Answer 3

ANd BTW, it is a bad idea to store names in one field. Makes it very hard to query. How would you effectively search for "Smith" withouth using wildcards as the first character which precludes the database from using the indexes. And if names are stored freeform like this, a person might be "John Smith", "Smith, John", "Smith , John" "Smith,John" etc and you wouldn't realize they were the same person. You should at a minumum have first_name, middle_name, last_name, personal_Suffix and then you can possibly have a calulated field that shows the complete name in the format you want displayed.

Answer 4

I am new to sql, and new to stackoverflow, but wouldn't this work?

SELECT taskid, name FROM table GROUP BY taskid