First, my table layout:
tblEquippedItems: <-(table name)
slotid (FK), itemid (FK), charid (FK) <-(attributes)
So I have this table that describes the items a character has equipped. Slot id refers to the item slot where the item, denoted by itemid resides. Charid describes the character that has these items equipped.
What I'd like to do is present the data in some format like so:
Character Name | Shoulder Item | Head Item | Leg Item | ... | <- Column Names
Zalbar | Shoulders of Penance | Helm of Penance | Leggings of Penance | ... | <- Data values
^- This was my feeble attempt to represent a query result set.
I'd like to represent this as a view so I don't have to have an unnecessary and unstable table in the database. The column names will have to come from querying the lookup table that stores the item slot names. Is there a way to programmatically define such a view? Dynamic SQL maybe? I'd love to avoid that...
SELECT [character].name AS [character_name], ISNULL([shoulder].name,'Nothing') AS [shoulder_item_name], ISNULL([head].name,'Nothing') AS [head_item_name], etc... FROM [character] LEFT JOIN [tblEquippedItems] AS [shoulder_item] ON [shoulder_item].charid = [character].id AND [shoulder_item].slotid = 1 LEFT JOIN [item] AS [shoulder] ON [shoulder].id = [shoulder_item].itemid LEFT JOIN [tblEquippedItems] AS [head_item] ON [head_item].charid = [character].id AND [head_item].slotid = 2 LEFT JOIN [item] AS [head] ON [head].id = [head_item].itemid etc...