Query a union table with fields as columns.

Question

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 1

Don't collapse by concatenation for storage of related records in your database. Its not exactly best practices.

What you're describing is a pivot table. Pivot tables are hard. I'd suggest avoiding them if at all possible.

Why not just read in your related rows and process them in memory? It doesn't sound like you're going to spend too many milliseconds doing this...

Answer 2

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+)

Answer 3

One option is to have Properties have a PropertyType, so:

table cards
integer ID | string name | ... (other properties common to all Cards)

table property_types
integer ID | string name | string format | ... (possibly validations)

table properties
integer ID | integer property_type_id | string name | string value
foreign key property_type_id references property_types.ID

table cards_properties
integer ID | integer card_id | integer property_id
foreign key card_id references cards.ID
foreign key property_id references propertiess.ID

That way, when you want to set a new property value, you can validate it by its type. One type could be "SpecialType" with an enumeration of values.

Answer 4

Related but values are values are kept in separate columns and you have know your "special types" a head of time:
http://stackoverflow.com/questions/17194/sql-query-to-compare-product-sales-by-month#17290

Otherwise I would do this with cursor in a stored procedure or preform the transformation in the business or presentation layer.

Stab at sql if you know all cases:

Select
    ID,NAME
    ,Synchro+DARK+Effect --  add a some substring logic to trim any trailing /'s
from
    (select
        ID
        ,NAME
        --may need to replace max() with min().
        ,MAX(CASE SPECIALTYPE WHEN "Synchro" THEN SPECIALTYPE +"/" ELSE "" END) Synchro
        ,MAX(CASE SPECIALTYPE WHEN "DARK" THEN SPECIALTYPE +"/" ELSE "" END) DARK
        ,MAX(CASE SPECIALTYPE WHEN "Effect" THEN SPECIALTYPE ELSE "" END) Effect
    from
        table
    group by
       ID
       ,NAME) sub1

Answer 5

I do have a type/format for my properties table, that way I know how to cast/evaluate when I'm dealing with an integer value. I wasn't sure if it was pertinent to this issue or not.