Combine Multiple Rows of Comment Text ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Combine Multiple Rows of Comment Text ?

Author

Topic

sphilip
Starting Member

8 Posts

Posted - 2009-02-20 : 14:48:28

I need some assistance. I've been writing in SQL for about a year now, and I've got a problem I can't seem to solve and don't really know where to begin. The issue is the comment section in the image below. I need the entire comment to be returned as one string of data, there are 4 in this instance, but each ItemCode can have as many or little as it needs depending on the users input.

Is there any way, maybe via a #temp table or something I could use to string these along into one line of text?

Example of desired result:
"Square Pattern (Heavy Fixed Circumference) Casing-Net Size 18, Shirred to 100 Feet Treated w/ Caramel Color, Smoke Flavor & Non-Allergen Release Agent (CS4N) 5 Sticks/Case"

Here is the code:
One other thing which is probably important, there can be many different ItemCodes listed on a SalesOrderNo. I would need the result set grouped by SalesOrderNo then by ItemCide


Declare @SO_Number as float
set @SO_Number = '57123'

SELECT distinct
	a.CustomerNo,
	a.SalesOrderNo, 
	b.ItemCode,
	replace(c.CommentText, '~;~', Char(10))as CommentText,
	c.SeqNumber
FROM mas90..SO_SalesOrderHeader a
left outer join mas90..SO_SalesOrderDetail b on b.SalesOrderNo = a.SalesOrderNo
left outer join mas90..IM_03ItemEndedDescription c on c.ItemNumber = b.ItemCode
WHERE  a.SalesOrderNo=@SO_Number
AND b.ItemType<>'4'
ORDER BY b.ItemCode,
	c.SeqNumber ASC

Here's an screenshot of the dataset:

Thank you for taking the time to read my post. Any help would be very much appreciated.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 15:08:35

what about seqnumber?

sphilip
Starting Member

8 Posts

Posted - 2009-02-20 : 15:17:45

quote:
Originally posted by sodeep

what about seqnumber?

I do not need SeqNumbert. I included it to show how the text needed to be ordered from lowest to highest in the final string but forgot to mention that, thank you.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 15:51:06

[code] --Create Function like this:

CREATE FUNCTION dbo.UDFComments
(
@SalesOrderNO VARCHAR(32),
@ItemCode VARCHAR(32)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Comments VARCHAR(8000)
SELECT @Comments = Coalesce(@Comments +' ', '')
+ CommentText
FROM yourtable
WHERE SalesOrderNo = @SalesOrderNO
AND ItemCode = @ItemCode
Order by SalesorderNo,ItemCode
RETURN @Comments
END
GO

Then use :

Select CustomerNo,SalesOrderNo,ItemCode,dbo.UDFComments(SalesOrderNo,ItemCode)
from your table
Group by CustomerNo,SalesOrderNo,ItemCode [/code]

sphilip
Starting Member

8 Posts

Posted - 2009-02-20 : 16:54:23

You sir are my savior today. I so much appreciate it. I've never used a UDF or coalesce before, I'm still unclear as to how it is working, but I'm going to start looking at it more.

Thank you, Thank you and Thank you!!!

After finishing up the select statements, here's the finished code...
UDF


CREATE FUNCTION dbo.UDFComments 
( 
    @SalesOrderNO VARCHAR(32),
    @ItemCode  VARCHAR(32) 
) 
RETURNS VARCHAR(8000) 
AS 
BEGIN 
    DECLARE @Comments VARCHAR(8000) 
    SELECT @Comments = Coalesce(@Comments, '') 
        + replace(b.CommentText, '~;~', Char(10)) 
        FROM mas90..SO_SalesOrderDetail a
	left outer join mas90..IM_03ItemEndedDescription b on b.ItemNumber = a.ItemCode
        WHERE a.SalesOrderNo = @SalesOrderNO
        AND a.ItemCode = @ItemCode
        Order by a.SalesorderNo, a.ItemCode
      RETURN @Comments 
END 
GO

And the select statement which calls it


Declare @SO_Number as float
set @SO_Number = '57123'

Select 	a.CustomerNo,
	b.SalesOrderNo,
	b.ItemCode,
	b.ItemType, 
	Dateadd(Month, 6, getdate()) as ExpDate,
	dbo.UDFComments(b.SalesOrderNo, b.ItemCode) as CommentText,
	a.CustomerPONo,
	a.ShipToAddress1, 
	a.ShipToAddress2, 
	a.ShipToAddress3, 
	a.ShipToCity + ', ' + a.ShipToState + ' ' + a.ShipToZipCode as 'CSZ',
	a.ShipToCountryCode
from mas90..SO_SalesOrderHeader a
left outer join mas90..SO_SalesOrderDetail b on b.SalesOrderNo = a.SalesOrderNo
where a.SalesOrderNo = @SO_Number
and b.ItemCode not like '/%'
Group by a.CustomerNo,
	b.SalesOrderNo,
	b.ItemCode,
	b.ItemType,
	a.ShipToAddress3, 
	a.CustomerPONo,
	a.ShipToAddress1, 
	a.ShipToAddress2,
	a.ShipToCity,
	a.ShiptoState,
	a.ShipToZipCode,
	a.ShipToCountryCode

and here's a portion of the resultset.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 16:56:28

You are most Welcome

.

sphilip
Starting Member

8 Posts

Posted - 2009-02-23 : 12:04:06

This is a little off subject but it's pertaining to the same query so I don't want to open another thread on it. I hope that is OK.

I need to remove the last portion of the comment text on some labels.

The screen shot below is from this line in the query:
dbo.UDFComments(b.SalesOrderNo, b.ItemCode) as CommentText,

.. Here's what I'd like to have returned.

If you take a close look at this resultset, at the end of the CommentText line you'll see the following:

10 donuts/case

500 Units/Case

5 rolls/case

8 Sticks/Case

The constants being

At the end of the line

Always ending in "Case"

Always a Number from 1-99999 followed by a space, followed by text ending in "Case"

Other things to note, The end of the line may not have this type of a string, if it does not it does not need to change.

I tried this and it works but I'd rather just remove it all together.
rtrim(replace(replace(dbo.UDFComments(b.SalesOrderNo, b.ItemCode), 'case', 'case; case Qty's on Sample Sales Orders Not Applicable'),'~;~', Char(10))) as CommentText,

Thank you for reading. Your assistance is appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources