I'm not quite sure if this is possible, or falls into the category of Pivot tables, but I figured I'd go to the pros to see.
I have three basic tables: Card, Property, and CardProperty. Since cards do not have the same properties, and often multiple values for the same property, I decided to use the union table approach to store data instead of having a really big column structure in my card table.
The property table is a basic keyword/value type table. So you have the keyword ATK and the value assigned to it. There is another property called SpecialType which a card can have multiple values for, such as "Sycnro" and "DARK"
What I'd like to do is create a view or stored procedure that gives me the Card Id, Card Name, and all the property keywords assigned to the card as columns and their values in the ResultSet for a card specified. So ideally I'd have a result set like:
ID NAME SPECIALTYPE
1 Red Dragon Archfiend Synchro
1 Red Dragon Archfiend DARK
1 Red Dragon Archfiend Effect
and I could tally my results that way.
I guess even slicker would be to simply concatenate the properties together based on their keyword, so I could generate a ResultSet like:
1 Red Dragon Archfiend Synchro/DARK/Effect
..but I don't know if that's feasible.
Help me stackoverflow Kenobi! You're my only hope.