Create View from field concatenation 1

[Buzzmeister14]

At the risk of soundnig dumb

I have read faq183-2146 and am still confused how to apply it to a view in SQL 2000

so will start from begining

I have the following Data returned when looking at a particular policy

EndorsementId | PolicyId | EndorsementText
------------------------------------------
69 | 77782 |BJ
70 | 77782 |BP
71 | 77782 |BK


I need a view created that would combine all the EndorsementText field into a comma seperated field

i.e

PolicyId | CombinedText
77782 | BJ,BP,BK

Any ideas appreciated have been going around this with various attempts but no success have lost my way a bit, and would like some direction
Thanks for any help

 

[JamesLean]

I think the only way you can do this in a single query, ie in a view, is to build a function that takes a policyid as input and returns the string of combinedtext. Then create a simple view calling the function.

CREATE FUNCTION dbo.fnGetDelimitedString (
	@id int
)
RETURNS varchar(1000)
AS
BEGIN
	--declare and initialise string variable
	DECLARE @s varchar(1000)

	SET @s = ''

	--get delimited values
	SELECT @s = @s + endorsementtext + ','
	FROM mytable
	WHERE policyid = @id

	--cut off trailing comma and return
	SET @s = LEFT(@s, LEN(@s) - 1)

	RETURN @s
END

CREATE VIEW myview
AS
SELECT DISTINCT policyid, dbo.fnGetDelimitedString(policyid) AS combinedtext
FROM mytable

--James

 

[Buzzmeister14]

Thanks for the help James,

doesn't need to placed in a view now

Only needs to concatenated on a Crystal report (so different forum for me to search through )

I wish some people would make up they're minds, prior to annoying others

Thanks anyway