I've almost got it now, but I have one more problem. I need to get the results for each saletype on one row for each saletype_id. I now get 5 rows for each saletype. I tried using distinct but that didn't work

WITH Names AS

(

SELECT saletype_Id,

LEFT( Item, 2) AS Country,

SUBSTRING( Item, 5, LEN(Item) - 5) AS name

FROM #saletype

CROSS APPLY dbo.DelimitedSplit8K( name, ';') s

WHERE LEN(Item) > 5

)

SELECT 'saletype' AS List

, n.saletype_id

--ISNULL( n.saletype_Id,'') AS saletype_id,

--ISNULL( n.Country,'') AS Country,

--ISNULL( name,'') AS name

, ToolTip_US_or_agreed= CASE WHEN n.country='US' THEN name ELSE '' END

, US= CASE WHEN n.country='US' THEN name ELSE '' END

, SW= CASE WHEN n.country='SW' THEN name ELSE '' END

, SP= CASE WHEN n.country='SP' THEN name ELSE '' END

, NL= CASE WHEN n.country='NL' THEN name ELSE '' END

, PL= CASE WHEN n.country='PL' THEN name ELSE '' END

, IT= CASE WHEN n.country='IT' THEN name ELSE '' END

, FR= CASE WHEN n.country='FR' THEN name ELSE '' END

, FI= CASE WHEN n.country='FI' THEN name ELSE '' END

, NO= CASE WHEN n.country='NO' THEN name ELSE '' END

, DA= CASE WHEN n.country='DA' THEN name ELSE '' END

, GE= CASE WHEN n.country='GE' THEN name ELSE '' END

, CZ= CASE WHEN n.country='CZ' THEN name ELSE '' END

, CZ= CASE WHEN n.country='CZ' THEN name ELSE '' END

, RU= CASE WHEN n.country='RU' THEN name ELSE '' END

, JP= CASE WHEN n.country='JP' THEN name ELSE '' END

FROM Names n

ORDER BY n.saletype_id