Combine multiple SQL2000 results into single string

Ok... so I have these tables

Line_Item
ID   |  NAME  
------------------
1         ITEM1
   
Line_Item_Text
L_I_ID   |  TYPE  |   TXT
-------------------------------------
1                 N         NOTE1
1                 N         NOTE2
1                 N         NOTE3
If Line_Item_Text TYPE ='N' it contains the text for Notes, which can contain multiple entries for any give Line_Item (L_I_ID).

So... In a view, joined to Line_Item I want to return all notes as single string delimited with spaces.

VIEW RETURNS

ID  |   NAME   |   NOTES
-------------------------------------------------------------
1        ITEM1        NOTE1 NOTE2 NOTE3

How do I concatenate the sub results like that in SQL?

function or the like would be fine.....

there are multiple txt types and i want to apply it across the board the sample above is extremely simplified.
GrizzlerAsked:
Who is Participating?
 
winheimConnect With a Mentor Commented:
I would generate a function like this

ALTER FUNCTION fn_Combine
(
	@ID int 
)
RETURNS nvarchar(max) 
AS

BEGIN

	DECLARE @sReturn nvarchar(max) = ''

	SELECT @sReturn  = @sReturn + ' ' + lit.TXT
	FROM Line_Item li
	INNER JOIN Line_Item_Text lit ON Li.ID = lit.L_I_ID
	WHERE li.ID = @ID

	RETURN RTrim(@sReturn)
END	

Open in new window


and then I would call the function like this

SELECT li.ID , li.Name ,  dbo.fn_Combine(li.ID)  AS Notes
FROM Line_Item li

Open in new window


is this a solution which is working with you?
0
 
GrizzlerAuthor Commented:
I had already got there,  but your answer was right on the money....

Here's what I did (names are a little different as it is actual)

ALTER FUNCTION [dbo].[fnGetConcText](@LID INT, @TYPE CHAR(4))

RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Names VARCHAR(8000)
set @Names=''

SELECT  @Names =@Names + ' *** ' + TXT
FROM LINE_ITEM_TEXT
WHERE TXT <>'' and txt is not null and LINE_ITEm_ID=@LID and TYPE=@TYPE

Set @NAMES= substring(@Names,6, 7994)

RETURN @NAMES
END


It deals with nulls and uses a different delimiter. It is running in test as we speak....
Curious in sql2000 what will happen if I exceed varchar(8000) and read somewhere not to use varchar(max)... Any more comments?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.