Concatenate Results Into A Single Field

Question

Post reply

Concatenate Results Into A Single Field

adi.cohen

Old Hand

Points: 371
More actions
June 15, 2008 at 12:37 am

#75236

Hi,
Say I have the two tables listed below in my database.
Table #1: persons
+----------------+
| id | name |
+----+-----------+
| 23 | Oluf |
| 24 | Christian |
| 25 | Jenny |
+----+-----------+
Table #2: person_pets
+----------------------+
| person_id | name |
+-----------+----------+
| 23 | Fido |
| 23 | Garfield |
| 23 | Casper |
| 25 | Shadow |
+-----------+----------+
Now, how can I construct a query which yields the results like this,
where the persons' pets are listed in a single field, separated with
a comma (or any other chosen character). Is it possible?
+---------------------------------------+
| id | name | pet_names |
+---------------------------------------+
| 23 | Oluf | Fido,Garfield,Casper |
| 24 | Christian | |
| 25 | Jenny | Shadow |
+---------------------------------------+
Will love to your help,
Thanks!
Adi

Viewing 15 posts - 1 through 15 (of 34 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

This is one way. Not the fastest, but not terrible, and easy to understand and remember:

--====== capture all of the persons

Select Id, [Name], Cast('' as varchar(255)) as pet_name

Into #temp

From persons

Alter Table #temp ADD Primary Key clustered (ID)

--====== add in all of the pet names

Update t

Set pet_names = pet_names+ ',' + pp.Name

From #temp t

Inner Join person_pets pp

ON pp.person_id = t.id

--====== remove the leading ','

Update #temp

Set pet_names = substring(pet_names, 2, len(pet_names-1))

Where pet_names like ',%'

--====== return it

Select * from #temp

Drop Table #temp

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 2

Here is an article that gives an overview of a couple of approaches to solve this problem:

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=CreateACommaDelimitedList&referringTitle=Home

When I am using SQL Server 2005 I tend toward the XML based solution.

Edit:

Barry has presented a pretty good solution. The advantage of Barry's solution is that it will work with older database versions.

adi.cohen Old Hand Points: 371 More actions · Answer 3

adi.cohen

Old Hand

Points: 371

June 15, 2008 at 8:46 am

#828971

Thanks!!

the XML solution was very helpfull 🙂

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

rbarryyoung (6/15/2008)
This is one way. Not the fastest, but not terrible, and easy to understand and remember:

Um... nope... a single update will update any given value once and only once. Update is not capable of self recursion even in the presence of a limited cross-join and will only put 1 pet name in the Person_Pets table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Arghh! Right you are, my bad. Thing is, although I thought that it would write more than once (thought I had seen it in a bad Update statement years ago), I did know that a DML statement cannot see the results of its changes as its input also (except for a recursive CTE), I just forgot... 🙁

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Jeff Moden (6/15/2008)
rbarryyoung (6/15/2008)
This is one way. Not the fastest, but not terrible, and easy to understand and remember:
Um... nope... a single update will update any given value once and only once. Update is not capable of self recursion even in the presence of a limited cross-join and will only put 1 pet name in the Person_Pets table.

You can kind of get around that with the "running totals" trick. Again - like Barry mentioned - not incredibly pretty, but will do the job:

drop table persons

drop table person_pets

drop table #temp

create table persons(id int, name varchar(20))

create table person_pets(person_ID int, name varchar(20))

insert persons

select 23 ,'Oluf' union all

select 24 ,'Christian' union all

select 25 ,'Jenny'

insert person_pets

select 23 ,'Fido' union all

select 23 ,'Garfield' union all

select 23 ,'Casper' union all

select 25 ,'Shadow'

--a couple of vars

declare @tmppetnames varchar(500)

set @tmppetnames='';

declare @dummy varchar(500)

declare @prevperson int

set @prevperson=0;

--====== capture all of the persons

Select person_Id,

identity(int,1,1) as rid,

[Name],

Cast('' as varchar(500)) as pet_name

Into #temp

From person_pets

create unique clustered index pktmp on #temp(person_Id,RID)

--====== add in all of the pet names

Update #temp

Set @tmppetnames=pet_name = case when @prevperson=person_ID then @tmppetnames +',' else '' end + Name,

@prevperson=person_id,

@dummy=@tmppetnames

From #temp with (index(pktmp), tablockx)

select persons.*, pets

from

persons

left outer join

(select person_id, max(pet_name) pets

from #temp

group by person_id) petlist

on persons.id=petlist.person_id

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

What's wrong with a good ol' concatenation function that works in 2k and 2k5?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

adi.cohen (6/15/2008)
Thanks!!
the XML solution was very helpfull 🙂

So... are you using SQL Server 2000 or not?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

Jeff Moden (6/15/2008)
What's wrong with a good ol' concatenation function that works in 2k and 2k5?

Are we thinking of the same thing? I thought the concatenation function was pretty RBAR?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

Jeff Moden (6/15/2008)
What's wrong with a good ol' concatenation function that works in 2k and 2k5?

Absolutely nothing. The two solutions are a bit of a toss-up from what I can tell: the longer the string to build (meaning fewer, but longer strings), the better the concat function works; the more groups, then better the running total seems to do.

The XML method tends to blow either away, but only happens in 2005.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

adi.cohen Old Hand Points: 371 More actions · Answer 11

Jeff Moden (6/15/2008)
adi.cohen (6/15/2008)
Thanks!!
the XML solution was very helpfull 🙂
So... are you using SQL Server 2000 or not?

I'm using SQL 2005 😎

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

Then the XML solution will work.

Just curious... why did you post on a 2k forum instead of a 2k5 forum? Folks wouldn't have wasted their time trying to come up with a 2k solution if you had...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 13

rbarryyoung (6/15/2008)
Jeff Moden (6/15/2008)
What's wrong with a good ol' concatenation function that works in 2k and 2k5?
Are we thinking of the same thing? I thought the concatenation function was pretty RBAR?

. . . and the peanut gallery gasps in horror . . . :w00t:

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 14

jcrawf02 (6/16/2008)
rbarryyoung (6/15/2008)
Jeff Moden (6/15/2008)
What's wrong with a good ol' concatenation function that works in 2k and 2k5?
Are we thinking of the same thing? I thought the concatenation function was pretty RBAR?
. . . and the peanut gallery gasps in horror . . . :w00t:

Heh... yep... except for the running total solution that Matt came up with, most concatenation functions are RBAR to one extent or another mostly because you've used a function to begin with.

I also know that the running total solution has to do the same number of concatenations as the concatenation function and it has to do a Grouped MAX(LEN) on top of that. It also has to store all of that information somewhere temporarily until the Grouped MAX(LEN) is calculated.

Guess I'll have to do a little performance testing to see which is actually faster.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions