The World is full of different wonderful approaches ;)
I don't like to use
CASE WHEN ... END[
^] statement. My purposal is to use
Common Table Expressions[
^] (CTE). It isn't exactly what you want, but it shows the way how to resolve your problem.
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'
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'
DECLARE @lng VARCHAR(30)
SET @lng='FR'
DECLARE @products VARCHAR(30)
SET @products = 'MT002,MZ412,MZ415'
;WITH SelectedProducts AS
(
SELECT LEFT(@products, CHARINDEX(',', @products)-1) AS ItemID, RIGHT(@products, LEN(@products)-CHARINDEX(',', @products)) AS Remainder
WHERE CHARINDEX(',', @products)>0
UNION ALL
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
(
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
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
)
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
Result:
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
By The Way: i would suggest you to change design of
Items
table. Instead using english names of colors, use them
ID
. Also,
colors
column should store values:
1#2#3
2#3
...etc
Why? It is easiest to fetch color by its
ID
, than its
name
(in English).
If you would like to fetch color Id's in any language, you can use
UNPIVOT[
^] table to achieve that:
SELECT ID, ColorName, Lngg
FROM (
SELECT *
FROM @colors
) AS pvt
UNPIVOT(ColorName FOR Lngg IN([EN],[DE],[FR],[PT])) AS unpvt
WHERE Lngg = @lng
Result (in case of 'FR' as an input parameter):
ID ColorName Lngg
1 blanc FR
2 noir FR
3 rouge FR