Many Fields into one Field

Question

Post reply

Many Fields into one Field

shane.barney

SSC Rookie

Points: 35
More actions
October 27, 2008 at 3:08 pm

#200032

Please help. I am somewhat new to MS SQL programming and need all of the help I can get. I have search for other posts on this subject but I am confused by the answers and unsure how they could be applied to what I am trying to do.
I have a table (tblIntakeMain) with a PK (IntakeMainID). I have a second table (tblPersonnel) with a PK (PersonnelID) and a foreign key field related to tblIntakeMain called fk_IntakeMainID. This is a one to many relationship (meaning there are anywhere from one to 20 records in tblPersonnel related to one record in tblIntakeMain).
Within tblPersonnel, I have field called FullName. I would like to be able to concentrate using IntakeMainID, all of the FullName fields into on record with each FullName record separated by a ",".
So, just as an example:
If tblPersonnel had the following data:
PersonnelIDfk_IntakeMainIDFullName
11Bob Smith
21Dow Jones
32Roberta Smith
41John Dow
53Perry Kerr
63Jen Chow
Result would be:
fk_IntakeMainIDFullName
1Bob Smith, Dow Jones, John Dow
2Roberta Smith
3Perry Kerr, Jen Chow
I am just not sure how to do this within SQL. And yes, before any asks, it has to be done on the server and not within the client application.
Would I use a function to do this? Stored procedure?
I am using MS SQL 2005. Thanks.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Here is some code to start with:

create table #TempTable (

PersonalID int,

fk_IntakeMainID int,

FullName varchar(64)

);

insert into #TempTable

select 1,1,'Bob Smith' union all

select 2,1,'Dow Jones' union all

select 3,2,'Roberta Smith' union all

select 4,1,'John Dow' union all

select 5,3,'Perry Kerr' union all

select 6,3,'Jen Chow';

select * from #TempTable;

SELECT

t1.fk_IntakeMainID,

STUFF((SELECT

',' + t2.FullName

FROM

#TempTable t2

WHERE

t1.fk_IntakeMainID = t2.fk_IntakeMainID

FOR XML PATH('')),1,1,'')

FROM

#TempTable t1

GROUP BY

t1.fk_IntakeMainID

drop table #TempTable;

😎

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Check out this article: http://www.sqlservercentral.com/articles/tamestrings6/263/

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

shane.barney SSC Rookie Points: 35 More actions · Answer 3

Thanks for the replies....

My problem is that the tblPersonnel table is 1000+ records and growing. So while the suggestions above would work, it would mean that I would have to code for each fk_IntakeMainID in the table. Plus, ever time a new record is added I would have to update the code. Is there a way to make it a dynamic table? Thanks again

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

I'm not sure what you are asking now. The code Lynn provided could be used to create a view or within a stored procedure that you would call whenever you need the data.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

shane.barney SSC Rookie Points: 35 More actions · Answer 5

What I am referring to is the this part of the code:

insert into #TempTable

select 1,1,'Bob Smith' union all

select 2,1,'Dow Jones' union all

select 3,2,'Roberta Smith' union all

select 4,1,'John Dow' union all

select 5,3,'Perry Kerr' union all

select 6,3,'Jen Chow';

Here he selects for each field value in tblPersonnel to build the TempTable. My problem is there are over 1000 records in tblPersonnel. Does this mean I need to manual code for each record? In other words, select 1,1,'Bob Smith' union all repeated 1000+ times. What I was hoping to be able to do was to create a dynamic table (call it Conce_tblPersonnel) that as records were added to the tblPersonnel the Conce_tblPersonnel is updated.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

Lynn provided that code as an EXAMPLE. You replace the #temptable reference in the select query with YOUR table name (tblPersonnel).

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

shane.barney (10/28/2008)
What I am referring to is the this part of the code:
insert into #TempTable
select 1,1,'Bob Smith' union all
select 2,1,'Dow Jones' union all
select 3,2,'Roberta Smith' union all
select 4,1,'John Dow' union all
select 5,3,'Perry Kerr' union all
select 6,3,'Jen Chow';
Here he selects for each field value in tblPersonnel to build the TempTable. My problem is there are over 1000 records in tblPersonnel. Does this mean I need to manual code for each record? In other words, select 1,1,'Bob Smith' union all repeated 1000+ times. What I was hoping to be able to do was to create a dynamic table (call it Conce_tblPersonnel) that as records were added to the tblPersonnel the Conce_tblPersonnel is updated.

I only had what you provided in your original post. The code provided was, as Jack explained, an example from which you could build.

Please take the time to read the following article, it provides good advice on how to best ask for help that best helps you.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

I'd be more the willing to help with more applicable code for your situation if you would provide the information recommended in the article.

😎