Combining Duplicate Product Numbers.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Combining Duplicate Product Numbers.

Author

Topic

wndrboy2k3
Starting Member

37 Posts

Posted - 2008-05-08 : 19:36:14

Hey guys! I am running into another problem, it seems the more i know the more I find out I need to learn!!

So here is the situation. I have 2 tables. One table is comprised of 2 columns

Product # | Variation #

Table # 2 is made up of 2 columns

Variation # | Variation Name

A product # can have multiple variations and therefore return multiple rows. If i use this very simple code.

SELECT dbo.TABLE1.Product_Number, dbo.TABLE2.VariationName
FROM dbo.TABLE1 
INNER JOIN dbo.TABLE 2 
ON dbo.TABLE1.VariationNumber = dbo.TABLE2.VariationNumber

cognos79
Posting Yak Master

241 Posts

Posted - 2008-05-08 : 22:14:27

Here is what I usually do...write a function that concatenates the productnames given the product id

SELECT dbo.TABLE1.Product_Number, fn_concatenate_prod_name(dbo.TABLE1.Product_Number)
FROM dbo.TABLE1
INNER JOIN dbo.TABLE 2
ON dbo.TABLE1.VariationNumber = dbo.TABLE2.VariationNumber

There might be an efficient solution than this...

wndrboy2k3
Starting Member

37 Posts

Posted - 2008-05-08 : 23:11:45

do you think there would be a way to use group by somehow?

davidmal
Starting Member

19 Posts

Posted - 2008-05-09 : 00:41:50

I can't think of a way to do this without using cursors. If you are searching a lot of data, cursors can make the query VERY SLOW. cognos79 had the correct suggestion. You can create a user defined function with the following SQL statements:

USE [YourDatabaseNameHere]
GO
CREATE FUNCTION [dbo].[fn_concatenate_prod_name]
(@Product_Number int)
RETURNS varchar(200)
AS
BEGIN
DECLARE @var varchar(50)
, @variations varchar(200)

DECLARE variations_cursor CURSOR FOR
SELECT dbo.Table2.VariationName
FROM dbo.Table1
INNER JOIN dbo.Table2
ON dbo.Table1.VariationNumber = dbo.Table2.VariationNumber
WHERE dbo.Table1.Product_Number = @Product_Number
OPEN variations_cursor
FETCH NEXT FROM variations_cursor
INTO @var
SELECT @var = LTRIM(RTRIM(@var))
SET @variations = ''
WHILE @@FETCH_STATUS = 0
BEGIN
IF @variations <> '' SELECT @variations = @variations + ' '
SELECT @variations = @variations + @var
FETCH NEXT FROM variations_cursor
INTO @var
SELECT @var = LTRIM(RTRIM(@var))
END
CLOSE variations_cursor
DEALLOCATE variations_cursor
RETURN @variations
END

Once the function is created you can use the query:

SELECT DISTINCT dbo.TABLE1.Product_Number, dbo.fn_concatenate_prod_name(dbo.TABLE1.ProductID) AS 'Variations'
FROM dbo.TABLE1
INNER JOIN dbo.TABLE2
ON dbo.TABLE1.VariationNumber = dbo.TABLE2.VariationNumber

Dave

wndrboy2k3
Starting Member

37 Posts

Posted - 2008-05-09 : 01:01:56

You jerk! that's totally worked!! I'm going to test it out with my actual datasource at the office tomorrow. What is all this negative press i hear about cursors though? is the slow down so dramatic that it should be avoided if possible?

davidmal
Starting Member

19 Posts

Posted - 2008-05-09 : 01:36:59

Cursors allow for good basic programmability but with a definite performance hit. Which is why I found a cursor-less solution to your problem.

I found this on the thread [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53293[/url] and converted it for your tables.
FUNCTION

USE [YourDatabaseNameHere]
GO
CREATE FUNCTION [dbo].[fn_ConcatVariationNames](@Product_Number int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SELECT @Output = COALESCE(@Output+' ', '') + RTRIM(LTRIM(t2.VariationName))
FROM dbo.Table1 t1
JOIN dbo.Table2 t2
ON t1.VariationNumber = t2.VariationNumber
WHERE t1.Product_Number = @Product_Number
ORDER BY t1.Product_Number
RETURN @Output
END

And the query
SELECT DISTINCT Product_Number, dbo.fn_ConcatVariationNames(Product_Number) as 'VariationNames'
FROM table1

Dave

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-09 : 04:44:52

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources