Concat row function help - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Concat row function help

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-02-27 : 10:28:48

Hi, everyone,
I got this function from Kristen from the next thread that will concat rows.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53293.
That function works beatifully agaisnt northwind.
I modified it to fit my use. However, I could not make it to work. Some times it is just as hard as to be a good copy-cat.
I have done the easy part, changed the input param type from int to varchar. Now here is the hard part, I tried to limit the total number of rows ( here is the tracking numbers) to up to 10. Anything over that limit will get a "too many to show" string.
Here is what I have:
CREATE FUNCTION dbo.ConcatTracking(@OrderID varchar(10))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SELECT @Output = COALESCE(@Output+', ', '') + CONVERT(varchar(20), SM.TrackingNo) + '(' +CONVERT(varchar(20), SM.Carrier) + ')'
FROM dbo.[stsShipMast] SM where CO_Number = @OrderID
ORDER BY SM.TrackingNo
--my solution is in here to have a var for the number of ',' in @Output,
--if it is over my limit, I reset the @Output to 'Too long to Show'
--I don't like it because it is after I have loaded up the @Output.
RETURN @Output
END
GO

And here is Kristen's original, if you want to test to limit the number of product name returned.

Thanks!

USE Northwind
GO
CREATE FUNCTION dbo.ConcatOrderProducts(@OrderID int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SELECT @Output = COALESCE(@Output+', ', '') + CONVERT(varchar(20), P.ProductName)
FROM dbo.[Order Details] OD
JOIN dbo.Products P
ON P.ProductID = OD.ProductID
WHERE OD.OrderID = @OrderID
ORDER BY P.ProductName

RETURN @Output
END
GO

SELECT OrderID, CustomerID, dbo.ConcatOrderProducts(OrderID)
FROM Orders
GO
DROP FUNCTION dbo.ConcatOrderProducts
GO

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-02-27 : 11:03:59

two things:

1) use a variable to keep track of results returned
2) limit to the top 11 only. no point in returning all, plus you may hit the varchar length limit.

so, we get something like this (new stuff is bolded):

CREATE FUNCTION dbo.ConcatTracking(@OrderID varchar(10))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Count int
SET @Count = 0
DECLARE @Output VARCHAR(8000)
SELECT Top 11 @Count = Count + 1, @Output = COALESCE(@Output+', ', '') + CONVERT(varchar(20), SM.TrackingNo) + '(' +CONVERT(varchar(20), SM.Carrier) + ')'
FROM dbo.[stsShipMast] SM where CO_Number = @OrderID
ORDER BY SM.TrackingNo

RETURN CASE WHEN @Count < 11 THEN @Output ELSE 'Too Long To Show' END
END
GO

Another option you have when doing it this way is to add "..." to the end of the output, or a "(more)" message, or something like that if you still want to show the first 10.

- Jeff
http://weblogs.sqlteam.com/JeffS

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-02-27 : 12:39:32

Jeff,

Thank you! It works perfectly!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources