Need to combine notes from multiple lines into one view 2

[JordanCN]

I have an program based on SQL 2000 back end. The program use to have a note field nvarchar of 600 characters where you could enter a note like so:

This is a test
Test Line 2
Test Line 3

And it would end up looking like this in the table:

PartNumber Notes
---------- -----------------------
XYZ-1234 This is a test[CRLF]Test Line 2[CRLF]Test Line 3

Now they change the structure and re-organized the data so it is now:

PartNumber Line Notes
---------- ---- -----------------------
XYZ-1234 0001 This is a test
XYZ-1234 0002 Test Line 2
XYZ-1234 0003 Test Line 3

In order for me to check if the notes and other items on XYZ-1234 matched ABC-9876 I need to have all these notes grouped into one field in a view or something that I can query against with MS Access and Crystal Reports. Is there a way to get a view of the new table to simulate the old?

 

[jshurst]

Yes, you can do this. You can use the STUFF() method. I'm not sure if it is available in SQL 2000 but I've used it before and it's very handy (although I thought it was pretty complex too - at least for what I was doing).

Here is a link to it:

http://msdn.microsoft.com/en-us/library/ms188043.aspx

I can post an example if you need me to.

Regards,

J

 

[simian336]

select distinct s1.partnumber,
STUFF((SELECT ' ' + s2.partnumber FROM dbo.table AS s2 WHERE s2.partnumber = s1.partnumber FOR XML PATH('')), 1, 1, '') AS CODES
from dbo.table s1


simi

 

[markros]

Since you want to insert CLRF, I encourage you to read carefully this excellent blog
Making a list and checking it twice that shows what to do in case XML PATH encodes the values (as it does with CLRF).

PluralSight Learning Library

 

[JordanCN]

Thanks, but this does not seem to be what I am looking for. I am not looking to substitute or modify the Part Number. I am looking to concatenate several records to appear as one

Right now when I run

Select * from PartNotes where PartNumber = "XYZ-124"

I get:

PartNumber Line Notes
---------- ---- -----------------------
XYZ-1234 0001 This is a test
XYZ-1234 0002 Test Line 2
XYZ-1234 0003 Test Line 3

But I want a view:

Select * from vwPartNotes where PartNumber = "XYZ-1234"

I get

PartNumber Notes
---------- -----------------------
XYZ-1234 This is a test[CRLF]Test Line 2[CRLF]Test Line 3

 

[gmmastros]

The advice given already assumes you are using SQL2005. Since you are using SQL2000, you'll need to use another approach. I suggest you read this thread:

thread183-1159740

It's an old thread, but it applies to SQL2000, so it should work out well for you. Pay particular attention, near the end, where a UDF is created.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

For SQL 2000 the only known technique is to use an UDF. Unfortunately, the ORDER is not 100% guaranteed with this approach.

Two blogs by Adam Haines show how to make it

http://jahaines.blogspot.com/2009/06/concatenating-column-values-part-1.html

http://jahaines.blogspot.com/2009/07/concatenating-column-values-part-2.html

PluralSight Learning Library