Concatenating a field from mulitple rows into one row

Question

Post reply

Concatenating a field from mulitple rows into one row

Alan_C

SSC Enthusiast

Points: 111
More actions
June 28, 2007 at 10:03 am

#124516

Hi
We are trying to build a datawarehouse. We have a person table which has refers to a conditions table. Basically each person may have multiple conditions. However each conidition is just a very short string and in reality only one or two conditions exist for each person. We would like to concatenate the conditions for each person into a single field in the person table. So we need to return all conditions for each person, get the condition field from each record and concatenate them then insert into the person table.
Is there any way of achieving this without using cursors (my cursor driven trial does 1000 rows in ten mins and we are talking about 400000 rows)
Thanks in advance

Viewing 7 posts - 1 through 6 (of 6 total)

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

mrpolecat SSCertifiable Points: 6964 More actions · Answer 1

Can you post a table structure and sample data?

Is there a maximum number of conditions one person could have?

Harley Arnett-275660 Right there with Babe Points: 799 More actions · Answer 2

Alan,

One way to handle it is with a UDF - thanks to whoever posted this -

CREATE FUNCTION fn_ConcatStrings

(@ID int)

RETURNS varchar(500)

AS

BEGIN

declare @String varchar(500)

select @String = coalesce(@String,'') + Col + ','

from TblConditions

where [Id] = @ID

if Right(@String,1) = ','

set @String = substring(@String,1,len(@String) -1)

return @String

END

GO

-- =============================================

-- Example to execute function

-- =============================================

SELECT [id], dbo.fn_ConcatStrings ([id])

from TblPersons

Good luck,

Harley

Alan_C SSC Enthusiast Points: 111 More actions · Answer 3

Thank you, that is a smart bit of code Harley. I've never seen coalesce used in that way before, not sure I understand completely why it works! Will do some background reading, however it does just what I want it to. Thanks again

Alan

Alan_C SSC Enthusiast Points: 111 More actions · Answer 4

Got it now. The coalesce is just to prevent trying to concatenate with a null (so would also work using Isnull). Basically the output from the select goes into @string + @string + new row. Simple and elegant!

Thanks again

Vladan SSC-Insane Points: 22072 More actions · Answer 5

It can be even nicer, without any IFs - COALESCE function takes care of the delimiter and will not place it at the end of the string:

CREATE FUNCTION fn_ConcatStrings (@ID int)

RETURNS varchar(500)

AS

BEGIN

declare @String varchar(500)

select @String = coalesce(@String +', ','') + Col

from TblConditions

where [Id] = @ID

return @String

END

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

Spot on, Vladan...

--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)