[SOLVED] Dynamically grab a specific column from a joined table

Dynamically grab a specific column from a joined table

I have a very complex query pulling data from almost 10 tables already and growing, and I need to achieve something unusual. The main table (the FROM table) has a field named SIZEPOS, which is an index number from 1 to 25. A joined table with alias siz has the appropriate joining field (some foreign ID) to the main table, and also fields, like SIZE1, SIZE2, ..., SIZE25, where each of them holds a size value, ie SIZE1 may hold the value 6, SIZE2 value 8, etc... So what I want is to include in my query the specific SIZE+[SIZEPOS] field of that joined table. In other words, if SIZEPOS holds the value 3 for a specific product, I want to grab the field siz.SIZE3 only out of all those SIZE1, SIZE2, SIZE3, SIZE4, etc...

I simplified the situation enough, to actually be able to prepare a fiddle for you! So what I need to achieve is to include for each row of the SELECT query the appropriate SIZE{\d} field instead of the hardcoded SIZE1 I used... From what I've read, the concept is called dynamic column, and needs a variable, and an EXEC command but my knowledge in SQL server is in its infancy so it's totally beyond me...

SELECT sub.ITEID, sub.SUBSTITUTECODE, prod.MAINSZLID, sub.SIZEPOS, siz.SIZE1
FROM @SUBSTITUTE AS sub
INNER JOIN @MATERIAL AS prod ON prod.ID = sub.ITEID
INNER JOIN @SIZELIST AS siz ON siz.CODEID = prod.MAINSZLID;

The fiddle can be found here

Solution

CHOOSE(sub.sizepos, SIZE1, SIZE2, SIZE3, SIZE4, SIZE5, SIZE6, SIZE7, SIZE8, SIZE9, SIZE10, SIZE11, SIZE12, SIZE13, SIZE14, SIZE15, SIZE16, SIZE17, SIZE18, SIZE19, SIZE20, SIZE21, SIZE22, SIZE23, SIZE24, SIZE25)

…