Table Join

Question

Post reply

Table Join

Jacqueline Lawson

SSC Enthusiast

Points: 135
More actions
March 2, 2005 at 7:25 am

#162878

I have a table with duplicate id fields (PPID) and I need to retrieve one row for each distinct id. My table columns are
(ppid, acctnum, time, duration)
100, abc, 10:00, 5
100, abc, 10:01, 0
200, def, 09:00, 20
200, ghi, 09:26, 1
200, xxx, 08:59, 0
300, xyz, 12:15, 30
400, lmn, 02:05, 2

I need to find a query to get a a result set like the following:
100, abc, 10:00, 5, abc, 10:01, 0
200, xxx, 08:59, 0, def, 09:00, 20, ghi, 09:26, 1
300, xyz, 12:15, 30
400, lmn, 02:05, 2
Can anyone tell me how to construct the table join?

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

CREATE FUNCTION [dbo].[fnItems] (@Id as int)

RETURNS varchar(500) AS

BEGIN

Declare @Items as varchar(500)

SET @Items = ''

Select @Items = @Items + CAST(acctnum as varchar(10)) + ',' + CAST([time] as varchar(10)) + ',' + CAST(Duration as varchar(10)) + ';' FROM dbo.YourTable WHERE PKCol = @Id

SEt @Items = LEFT(@Items, ABS(LEN(@Items) - 2))

Return @Items

END

Select PPID, dbo.fnItems(PPID) as Items from dbo.YourTable

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Actually it might run faster like this :

Select A.PPID, dbo.fnItems(A.PPID) as Items from

(Select DISTINCT PPID FROM dbo.YourTable) A

ORDER BY A.PPID

rhunt SSCertifiable Points: 5868 More actions · Answer 3

Remi, nice function.

I think you meant your final set to be:

SEt @Items = LEFT(@Items, ABS(LEN(@Items) - 1))

Right?

Jacqueline Lawson SSC Enthusiast Points: 135 More actions · Answer 4

Thanks. That's a great function. It's exactly what I needed. You saved me so much time. Thanks again.

Jacqueline

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

hmm ya... I'm used to return strings that end with ', '.. but even then LEN() would chop the last space.

To sum up the best approach in all situations would be something like this :

SET @Items = LEFT(@Items, ABS(DATALENGTH(@Items) - @LengthOfSeparator))

Has anyone got a better idea?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

BTW... I forgot to say that something like this is better handled at the client side of the app because it's really presentation stuff. I only HAD TO use this method once because I needed to create a flat file to send info to another application and I preffered generating the text on the server because it avoided opening 20 recordsets and doing 1000's of roundtrips when only 1 could do it. The only other time I would find this usefull is in a report where you can't always control what's going on.