sql server: select colors in stated language for list of items

go
Create Table colors (ID int identity(1,1), EN varchar(100), DE varchar(100),FR	varchar(100), PT varchar(100))
Insert colors values('white','weiss','blanc','branco')
Insert colors values('black','schwarz','noir','preto')
Insert colors values('red','rot','rouge','vermelho')
select * from colors 
go
Create Table Items(ID int identity(1,1),ItemID varchar(100), Model varchar(100),Colors varchar(100))
 
Insert Items values('MT002','S9500','black#white#red')
Insert Items values('MZ412','W8','black#red')
Insert Items values('MZ415','W8s','black#white')
Insert Items values('MZ499','N9500','red')


go
 alter Function GetColorCode
 (
 @ItemID varchar(100),
 @Language varchar(100)
 )
 returns varchar(max)
 as
 Begin 
       Declare @retValue varchar(max)
		
	set @retValue = ''
	Declare @Length int
	Declare @ColorSeperatorpos int
	Declare @ColorValue varchar(100)
	Declare @CurrentColor varchar(100)
	Select @ColorValue  = Colors from Items where  ItemID   = @ItemID
		
	set  @ColorSeperatorpos = CHARINDEX('#', @ColorValue)
	While  (@ColorSeperatorpos  > 0)
	Begin 
  	        Select @CurrentColor  = SUBSTRING(@ColorValue, 0, @ColorSeperatorpos)
		Select @retValue =@retValue   + ( case 
		                                   when @Language = 'EN' then en 
  					           when @Language = 'DE' then de 
  					           when @Language = 'FR' then fr 
 						   when @Language = 'PT' then PT
						end  )   + '#'
			from colors where en =  @CurrentColor

			Set @ColorValue = SUBSTRING(@ColorValue, @ColorSeperatorpos+1, LEN(@ColorValue)- @ColorSeperatorpos)
			Set @ColorSeperatorpos =  CHARINDEX('#', @ColorValue)
		End
		
		if(@ColorValue <> '' )
		Begin 
		     Select @retValue =@retValue   + ( case 
							when @Language = 'EN' then en 
							when @Language = 'DE' then de 
							when @Language = 'FR' then fr 
							when @Language = 'PT' then PT
							end  )   + '#'
			from colors where en =  @ColorValue
		End
		
		return Substring(@retValue,0,LEN(@retValue) )
 End

go 
 
Declare @Language varchar(100) ='fr'
SELECT Items.Model, dbo.GetColorCode( Items.ItemID, @Language) FROM Items 

--colors table variable
DECLARE @colors TABLE (ID INT IDENTITY(1,1), EN VARCHAR(30), DE VARCHAR(30), FR VARCHAR(30), PT VARCHAR(30))

INSERT INTO @colors (EN, DE, FR, PT)
SELECT 'white', 'weiss', 'blanc', 'branco'
UNION ALL SELECT 'black', 'schwarz', 'noir', 'preto'
UNION ALL SELECT 'red', 'rot', 'rouge', 'vermelho'

--items table variable
DECLARE @items TABLE(ID INT IDENTITY(1,1), ItemID VARCHAR(30), Model VARCHAR(30), Colors VARCHAR(100))

INSERT INTO @items (ItemID, Model, Colors)
SELECT 'MT002', 'S9500', 'black#white#red'
UNION ALL SELECT 'MZ412', 'W8', 'black#red'
UNION ALL SELECT 'MZ415', 'W8s', 'black#white'
UNION ALL SELECT 'MZ499', 'N9500', 'red'

--input parameters:
DECLARE @lng VARCHAR(30)
SET @lng='FR'

DECLARE @products VARCHAR(30)
SET @products = 'MT002,MZ412,MZ415'

--first CTE
--split ItemID into separate rows
;WITH SelectedProducts AS
(
	--initial values
	SELECT LEFT(@products, CHARINDEX(',', @products)-1) AS ItemID, RIGHT(@products, LEN(@products)-CHARINDEX(',', @products)) AS Remainder
	WHERE CHARINDEX(',', @products)>0
	UNION ALL
	--recursive part
	SELECT LEFT(Remainder, CHARINDEX(',', Remainder)-1) AS ItemID, RIGHT(Remainder, LEN(Remainder)-CHARINDEX(',', Remainder)) AS Remainder
	FROM SelectedProducts
	WHERE CHARINDEX(',', Remainder)>0
	UNION ALL
	SELECT Remainder AS ItemID, NULL AS Remainder
	FROM SelectedProducts
	WHERE CHARINDEX(',', Remainder)=0
), CurrentColors AS
--second CTE, split colors 
	(
		--initial value
		SELECT sp.ItemID, i.Model, LEFT(i.Colors, CHARINDEX('#',i.Colors)-1) AS Color, RIGHT(i.Colors, LEN(i.Colors)-CHARINDEX('#',i.Colors)) AS CRemainder
		FROM SelectedProducts AS sp INNER JOIN @items AS i ON i.ItemID = sp.ItemID
		WHERE CHARINDEX('#',i.Colors)>0
		UNION ALL
		--recursive part
		SELECT ItemID, Model, LEFT(CRemainder, CHARINDEX('#',CRemainder)-1) AS Color, RIGHT(CRemainder, LEN(CRemainder)-CHARINDEX('#',CRemainder)) AS CRemainder
		FROM CurrentColors
		WHERE CHARINDEX('#',CRemainder)>0
		UNION ALL
		SELECT ItemID, Model, CRemainder AS Color, NULL AS CRemainder
		FROM CurrentColors
		WHERE CHARINDEX('#',CRemainder)=0
	)
--final result set
		SELECT cc.ItemID, cc.Model, cc.Color AS EN, c.DE, c.FR, c.PT
		FROM CurrentColors AS cc INNER JOIN @colors AS c ON cc.Color=c.EN
		ORDER BY cc.ItemID

ItemID  Model   EN      DE      FR      PT
MT002	S9500	black	schwarz	noir	preto
MT002	S9500	white	weiss	blanc	branco
MT002	S9500	red	rot	rouge	vermelho
MZ412	W8	red	rot	rouge	vermelho
MZ412	W8	black	schwarz	noir	preto
MZ415	W8s	black	schwarz	noir	preto
MZ415	W8s	white	weiss	blanc	branco

1#2#3
2#3
...etc

SELECT ID, ColorName, Lngg
FROM (
    SELECT *
    FROM @colors
    ) AS pvt
UNPIVOT(ColorName FOR Lngg IN([EN],[DE],[FR],[PT])) AS unpvt
WHERE Lngg = @lng

ID      ColorName	Lngg
1	blanc		FR
2	noir		FR
3	rouge		FR