I would be inclined to use a function. Something like:

SET QUOTED_IDENTIFIER, ANSI_NULLS ON

GO

CREATE FUNCTION dbo.GetAssetNames

(

&nbsp&nbsp&nbsp&nbsp@policy_number varchar(20) -- or whatever the datatype of policy_number is.

)

RETURNS varchar(8000)

AS

BEGIN

&nbsp&nbsp&nbsp&nbspDECLARE @AssetNames varchar(8000)

&nbsp&nbsp&nbsp&nbspSET @AssetNames = ''

&nbsp&nbsp&nbsp&nbspSELECT @AssetNames = @AssetNames

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp+ CASE

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspWHEN CHARINDEX('(', asset_name) > 0

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspTHEN LEFT(asset_name, CHARINDEX('(', asset_name) - 1)

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspELSE asset_name

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspEND

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp+ '#'

&nbsp&nbsp&nbsp&nbspFROM asset

&nbsp&nbsp&nbsp&nbspWHERE policy_number = @policy_number

&nbsp&nbsp&nbsp&nbspRETURN LEFT(@AssetNames, LEN(@AssetNames) - 1)

END

GO

SELECT

&nbsp&nbsp&nbsp&nbsppolicy_number

&nbsp&nbsp&nbsp&nbsp,ulp_order_id

&nbsp&nbsp&nbsp&nbsp,dbo.GetAssetNames(policy_number) AS asset_name

FROM ulp_order