Merging rows in a join

Question

Post reply

Merging rows in a join

Phantom-450115

SSC Rookie

Points: 28
More actions
October 30, 2007 at 5:05 am

#199520

Is there any easy way to have a join merge several rows into one row containing - eg. - a comma-delimited set of results.
Two tables: Person and PhoneNumber, with a foreign key in PhoneNumber linking back to Person, so a person can have multiple phone numbers. If I do a simple join (in this case, a left outer join), then I'll get back something like this (for Mr Smith who has two phone nos and Mr Jones who has none):
NAME NUMBER
Mr. Smith 500 999 9999
Mr. Smith 500 999 9998
Mr. Jones NULL
What I really want is this:
NAME NUMBERS
Mr. Smith 500 999 9999, 500 999 9998
Mr. Jones NULL
In other words, each distinct person only appears once, and one of the returned fields contains a list of all his numbers.
Is there any easy way to do this with a SQL query?

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

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

Johannes Fourie Default port Points: 1470 More actions · Answer 1

Hi, the easiest would probably be to crate a UDF that will return a comma delimited column with all the phone numbers.

There are 2 ways you can do it, 1 way is to have a cursor and concatenate a string which is returned by the function.

Another cool way of doing it in SQL is like this:

Put this into a UDF to return a VARCHAR(2000) with all the phone numbers for that Person

DECLARE @PhoneNumbers VARCHAR(2000)

SET @PhoneNumbers = ''

SELECT @PhoneNumbers = @PhoneNumbers + Number+ ',' FROM dbo.PhoneNumber Where Person = @Person

SELECT LEFT(@PhoneNumbers, LEN(@PhoneNumbers)-1)

RETURN @PhoneNumbers

In this query SQL automatically adds all the values returned by the select into a variable. The last step just removes the extra comma

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 2

under 2005 you could use a join, and use a trick with xml path to concatenate the column values.

Under 2000, where xml path is not supported you could create a user defined function to do the concatenation for you. There are examples for both of the above on http://www.sqlservercentral.com/Forums/Topic391111-338-1.aspx

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Johannes Fourie Default port Points: 1470 More actions · Answer 3

Thanks Andras, I did not know about the XML PATH way. It seems to be working a lot better.

I tested it on a small set of data to compare the affect it has on the CPU. I have noticed before when working on large sets of data using the UDF to return a concatenated column it consumes a lot of CPU.

This is what i found:

Using XML Path:

CPU time = 70 ms, elapsed time = 67 ms.

Using UDF

CPU time = 401 ms, elapsed time = 413 ms.

So its definitely a lot better

Thanks again

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 4

Thanks Andras, I did not know about the XML PATH way. It seems to be working a lot better.
I tested it on a small set of data to compare the affect it has on the CPU. I have noticed before when working on large sets of data using the UDF to return a concatenated column it consumes a lot of CPU.

hey Andras,

Can you Please give one Demo SQL Script for

"XML PATH" & UDF with same operation.so i can compare it.

its sounds good.

Cheers!

Sandy.

--

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 5

Sandy Millon. (10/30/2007)
Thanks Andras, I did not know about the XML PATH way. It seems to be working a lot better.
I tested it on a small set of data to compare the affect it has on the CPU. I have noticed before when working on large sets of data using the UDF to return a concatenated column it consumes a lot of CPU.
hey Andras,
Can you Please give one Demo SQL Script for
"XML PATH" & UDF with same operation.so i can compare it.
its sounds good.
Cheers!
Sandy.

create table demo (nonuniqueid int, data char)

GO

insert into demo values (1, 'a')

insert into demo values (1, 'b')

insert into demo values (1, 'c')

insert into demo values (2, 'a')

insert into demo values (2, 'b')

insert into demo values (3, 'a')

GO

-- what I'd like to see is

-- 1 a,b,c

-- 2 a,b

-- 3 a

-- three rows, where the second column is concatenated

-- With functions:

CREATE FUNCTION ConcatenateCols (@nonuniqueid int)

RETURNS varchar(150)

AS

BEGIN

DECLARE @result varchar(500), @delimiter char

SET @delimiter = ','

SELECT @result = COALESCE(@result + @delimiter, '') + demo.data

from demo where nonuniqueid = @nonuniqueid

RETURN(@result)

END

SELECT nonuniqueid

, dbo.ConcatenateCols(nonuniqueid)

FROM demo

GROUP BY nonuniqueid

-- With xpath:

SELECT nonuniqueid

,STUFF((

SELECT ',' + c.data

FROM demo c

WHERE c.nonuniqueid = c2.nonuniqueid

FOR XML PATH('')

),1,1,'') AS CustList

FROM demo AS c2

GROUP BY c2.nonuniqueid

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 6

Hey Andras,

Cool Script, Let me check this.

with few more R&D today.

Cheers! 🙂

Sandy.

--

Phantom-450115 SSC Rookie Points: 28 More actions · Answer 7

Wow, that's quite a discussion this has started 🙂

Thanks for all your input guys. I'm now busily trying out UDF's. Unfortunately the XML solution won't work for me - as I just found out the DB is a SQL Server 2000 one, not the 2005 one I thought it was 🙁