CODEWITHSUNDEEP
sqlsql-servert-sqlstring-concatenation
Matthew Jones
Matthew Jones

Reputation: 26190

Combine Multiple Fields into One Text Field in SQL Server

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!

Upvotes: 0

Views: 13472

Answers (4)

HLGEM
HLGEM

Reputation: 96552

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.

Upvotes: 1

KM.
KM.

Reputation: 103579

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)

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89651

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

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

Upvotes: 4

Another Guy
Another Guy

Reputation:

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

SELECT taskid, name FROM table GROUP BY taskid

Upvotes: 0

Related Questions