DROP FUNCTION CG_PARSE_ARRAY
GO

CREATE FUNCTION CG_PARSE_ARRAY (
	@array VARCHAR(8000)
	, @separator CHAR(1) = ' '
	)
RETURNS @return TABLE (
		[ID] INT IDENTITY(1,1) PRIMARY KEY
		, [value] VARCHAR(8000)
		)
AS BEGIN 

	-- @Array is the array we wish to parse
	-- @Separator is the separator charactor such as a comma

	DECLARE @separator_position INT -- This is used to locate each separator character
	DECLARE @array_value VARCHAR(8000) -- this holds each array value as it is returned

	-- For my loop to work I need an extra separator at the end.  I always look to the
	-- left of the separator character for each array value
	SET @array = @array + @separator

	-- Loop through the string searching for separtor characters
	WHILE ( PATINDEX('%' + @separator + '%' , @array) <> 0 ) BEGIN

		-- patindex matches the a pattern against a string
		SELECT @separator_position =  PATINDEX('%' + @separator + '%' , @array)
		SELECT @array_value = left(@array, @separator_position - 1)

		-- This is where you process the values passed.
		-- Replace this select statement with your processing
		-- @array_value holds the value of this element of the array
		INSERT @return ([value]) SELECT @array_value

		-- This replaces what we just processed with and empty string
		SELECT @array = STUFF(@array, 1, @separator_position, '')
	END

	RETURN 
END
GO

DECLARE @dataSet TABLE (
	[cDef] INT 
	, [description] VARCHAR(8000)
	)

INSERT @dataSet ([cDef], [description])
      SELECT 1500, 'Hat Tie Shoes Boots'
UNION SELECT 1500, 'Tie Blue Red Cat'
UNION SELECT 1500, 'Shoes Red Fish Cup'
UNION SELECT 1650, 'Hello Seed Tree'
UNION SELECT 1650, 'Seed Pencil Paper'
UNION SELECT 1650, 'Knife Phone Tree'

SELECT * FROM @dataSet

-- Make the Map
DECLARE @map TABLE ([cDef] INT, [description] VARCHAR(8000))

-- Cursor (pah -- 2000 LONG FOR CROSS APPLY!)
DECLARE popCursor CURSOR LOCAL READ_ONLY FOR
SELECT [cDef], [description] FROM @dataSet

DECLARE @code INT
DECLARE @description VARCHAR(8000)
OPEN popCursor

	FETCH NEXT FROM popCursor INTO @code, @description

	WHILE ( @@FETCH_STATUS = 0 ) BEGIN

		INSERT @map ([cDef], [description])
		SELECT
			@code
			, p.[value]
		FROM
			dbo.CG_PARSE_ARRAY (@description, ' ') AS p
		WHERE
			NOT EXISTS (
				SELECT 1
				FROM @map AS m
				WHERE
					m.[cDef] = @code
					AND m.[description] = p.[value]
				)

		FETCH NEXT FROM popCursor INTO @code, @description
	END

CLOSE popCursor
DEALLOCATE popCursor

SELECT * FROM @map


-- Concatenate
SELECT
	[cDef]
	, (
		SELECT [description] + ' '
		FROM @map AS m2
		WHERE m2.[cDef] = m.[cDef]
		ORDER BY m2.[description]
		FOR XML PATH('')
		) AS [descriptions]
FROM
	(
	SELECT DISTINCT [cDef] FROM @map
	)
	AS m

quote:

---

Originally posted by robvolk

FYI TransactCharlie, FOR XML PATH is a 2005 feature.

---

quote:

---

Originally posted by robvolk

Do you need to maintain the order, i.e. 1500 Hat Tie Shoes Boots Blue Red Cat Fish Cup, or could they be in alphabetical order?

---

-- creates a "Tally" table (#n) of numbers
-- skip this part if you already have a tally table, and change the name below
create table #n(n smallint not null primary key)
insert #n values(1)
while (select max(n) from #n)<8000
insert #n select n+(select max(n) from #n) from #n where n+(select max(n) from #n)<=8000

-- sample data
create table #a(code int not null, description varchar(1024) not null)
insert #a (code, description)
select 1500, 'Hat Tie Shoes Boots' union all 
select 1500, 'Tie Blue Red Cat' union all 
select 1500, 'Shoes Red Fish Cup' union all 
select 1650, 'Hello Seed Tree' union all 
select 1650, 'Seed Pencil Paper' union all 
select 1650, 'Knife Phone Tree'

-- temp table to hold split words
create table #results(code int not null, word varchar(128) not null)

-- query to split words
insert #results(code, word)
select distinct code, ltrim(rtrim(substring(d, n, charindex(' ', d, n+1)-n)))
from (select code, ' '+description+' ' d from #a) z  -- subquery to simplify the substring expression
cross join #n
where n<len(d) and substring(d,n-1,1)=' ' 

-- temp table to hold concatenated results
create table #merge (code int not null primary key, description varchar(8000))

insert #merge select code, min(word) from #results group by code

-- loop through words, concatenate, then remove from #results
while @@rowcount>0 begin
	delete r from #results r 
	inner join #merge m on m.code=r.code
	where m.description like '%'+r.word

	update m set description=description+' '+r.word
	from #merge m inner join
	(select code, min(word) word from #results group by code) r on m.code=r.code
end

select * from #merge