Query a union table with fields as columns

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.

Answer

Is this for SQL server?

If yes then

Concatenate Values From Multiple Rows Into One Column (2000)
Concatenate Values From Multiple Rows Into One Column Ordered (2005+)

Advertisement

Answer