Complicated Query

Question

Complicated Query

Jim C-203340

SSCommitted

Points: 1716
More actions
July 19, 2007 at 1:52 pm

#61315

i have a query that is causing me trouble. lets say i have two tables
table1 (order# int, item# int)
table2 (order# int, item# int, Attribute string)
Not every entry in table1 must have an entry in table2, so i'm using a left join.
There can be multiple rows in table2 for a row in table1. This of course results in duplicate entries from table1.
I need to return only 1 row for table1. But, somehow combine all found attributes into one column in my restults.
The only way that i've been able to achieve this, is with a UDF.
select order#, item#, getAttributeList(order#, item#) as attributes from table1
within the function, i am using a cursor to lookup all attributes for the order#/item# and then loop through them and build a comma delimited string that is returned to my query. It's terribly slow, as i knew it would be.
Does anyone know a way to concatenate strings without a cursor? some sort of a char() aggregation?
Thanks for your help!
Jim
Regards, Jim C

Viewing 10 posts - 1 through 9 (of 9 total)

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

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 1

Jim,

Is there a finite number of possible records in table2 that match up to the records in table1? If so you could do something like derived tables to return each different possibility...

Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...

Jim C-203340 SSCommitted Points: 1716 More actions · Answer 2

Thanks for the suggestion, Ben. Unfortunately, there can be any number of attributes for each order.

Regards, Jim C

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 3

Ah yeah then I believe the only way is with a cursor...

Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...

Jim C-203340 SSCommitted Points: 1716 More actions · Answer 4

yuk! that's what i was afraid of.

Thanks for your help, Ben.

Regards, Jim C

bdohmen SSCrazy Points: 2996 More actions · Answer 5

Assuming the concatenated string of attributes will be less than 8000 characters, you don't need to do the cursor. Your function could look something like the following.

CREATE FUNCTION dbo.AllAttributes

(@Order INT)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @Attribs VARCHAR(8000)

SELECT @Attribs=''

SELECT @Attribs=@Attribs+Attribute+', '

FROM Table2

WHERE [Order#]=@Order

IF LEN(@Attribs)>0

SELECT @Attribs=SUBSTRING(RTRIM(@Attribs),1,LEN(RTRIM(@Attribs))-1)

RETURN @Attribs

END

Brian

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 6

I'm a bit confused by your function Brian. To me it looks like this would need to be called for every one of the attributes, so in the main query you would have to first find the value then find out how many attributes it has then call this once for each attribute incrementing the @Order var. I guess this could be done in a loop instead of a cursor but still it takes N number of iterations...

Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...

John Rowan SSC Guru Points: 56440 More actions · Answer 7

Brian's function should work here. Here's an example of how ( I tweaked his original to include a join on the ItemNum column and to use my example data column names):

--Brian's original function tweaked to include ItemNum

CREATE FUNCTION dbo.AllAttributes (@Order INT, @item int)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @Attribs VARCHAR(8000)

SELECT @Attribs=''

SELECT @Attribs=@Attribs+Attribute+', '

FROM Table2

WHERE <A href="mailtorderNum=@Order">OrderNum=@Order AND ItemNum = @Item

IF LEN(@Attribs)>0

SELECT @Attribs=SUBSTRING(RTRIM(@Attribs),1,LEN(RTRIM(@Attribs))-1)

RETURN @Attribs

END

IF EXISTS(select 1 from dbo.sysobjects where id = object_id('Table1'))

drop table table1

IF EXISTS(select 1 from dbo.sysobjects where id = object_id('Table2'))

drop table table2

CREATE TABLE Table1 (orderNum int, itemNum int)

CREATE TABLE Table2 (orderNum int, itemNum int, Attribute varchar(20))

INSERT INTO Table1

SELECT 1,1 UNION ALL

SELECT 1,2 UNION ALL

SELECT 1,3 UNION ALL

SELECT 1,4 UNION ALL

SELECT 1,5 UNION ALL

SELECT 2,1 UNION ALL

SELECT 2,2

INSERT INTO Table2

SELECT 1,1, 'Attribute1' UNION ALL

SELECT 1,1, 'Attribute2' UNION ALL

SELECT 1,1, 'Attribute3' UNION ALL

SELECT 1,1, 'Attribute4' UNION ALL

SELECT 1,1, 'Attribute5' UNION ALL

SELECT 2,1, 'Attribute6' UNION ALL

SELECT 2,1, 'Attribute7'

SELECT DISTINCT t1.OrderNum,

t1.ItemNum,

dbo.AllAttributes(t1.OrderNum,t1.ItemNum) as 'Attributes'

FROM Table1 t1

LEFT JOIN Table2 t2

ON t1.OrderNum = t2.OrderNum AND t1.ItemNum = t2.ItemNum

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Jim C-203340 SSCommitted Points: 1716 More actions · Answer 8

Brian and John, thank you guys for providing this. I hadn't used the

SELECT @Attribs=@Attribs+Attribute+', ' FROM Table2

syntax in a long time and had forgotten about it. This works perfectly and makes my run times oh so much faster.

Thanks!

Jim

Regards, Jim C

Jim C-203340 SSCommitted Points: 1716 More actions · Answer 9

Ben,

Brian's query works because of the set-based nature of SQL. The looping that you describe is all handeld behind the scenes for us. Thanks for helping out with this one.

Jim

Regards, Jim C