<p>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.</p>

<p>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.</p>

<p>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"</p>

<p>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:</p>

<pre><code>ID  NAME                   SPECIALTYPE
1   Red Dragon Archfiend   Synchro
1   Red Dragon Archfiend   DARK
1   Red Dragon Archfiend   Effect
</code></pre>

<p>and I could tally my results that way.</p>

<p>I guess even slicker would be to simply concatenate the properties together based on their keyword, so I could generate a ResultSet like:</p>

<pre><code>1  Red Dragon Archfiend   Synchro/DARK/Effect
</code></pre>

<p>..but I don't know if that's feasible.</p>

<p>Help me stackoverflow Kenobi! You're my only hope.</p>

[{'answer_id': 18236, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': "<p>Don't collapse by concatenation for storage of related records in your database.  Its not exactly best practices.  </p>\n\n<p>What you're describing is a pivot table.  Pivot tables are <em>hard</em>.  I'd suggest avoiding them if at all possible.  </p>\n\n<p>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...</p>\n"}, {'answer_id': 18239, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 2, 'selected': True, 'text': '<p>Is this for SQL server?</p>\n\n<p>If yes then</p>\n\n<p><a href="http://wiki.lessthandot.com/index.php/Concatenate_Values_From_Multiple_Rows_Into_One_Column" rel="nofollow noreferrer">Concatenate Values From Multiple Rows Into One Column (2000)</a><br>\n<a href="http://wiki.lessthandot.com/index.php/Concatenate_Values_From_Multiple_Rows_Into_One_Column_Ordered" rel="nofollow noreferrer">Concatenate Values From Multiple Rows Into One Column Ordered (2005+)</a></p>\n'}, {'answer_id': 18245, 'author': 'James A. Rosen', 'author_id': 1190, 'author_profile': 'https://Stackoverflow.com/users/1190', 'pm_score': 0, 'selected': False, 'text': '<p>One option is to have Properties have a PropertyType, so:</p>\n\n<pre><code>table cards\ninteger ID | string name | ... (other properties common to all Cards)\n\ntable property_types\ninteger ID | string name | string format | ... (possibly validations)\n\ntable properties\ninteger ID | integer property_type_id | string name | string value\nforeign key property_type_id references property_types.ID\n\ntable cards_properties\ninteger ID | integer card_id | integer property_id\nforeign key card_id references cards.ID\nforeign key property_id references propertiess.ID\n</code></pre>\n\n<p>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.</p>\n'}, {'answer_id': 18262, 'author': 'jason saldo', 'author_id': 1293, 'author_profile': 'https://Stackoverflow.com/users/1293', 'pm_score': 1, 'selected': False, 'text': '<p>Related but values are values are kept in separate columns and you have know your "special types" a head of time:<br/> <a href="https://stackoverflow.com/questions/17194/sql-query-to-compare-product-sales-by-month#17290">SQL query to compare product sales by month</a></p>\n\n<p>Otherwise I would do this with cursor in a stored procedure or preform the transformation in the business or presentation layer.</p>\n\n<p>Stab at sql if you know all cases:</p>\n\n<pre><code>Select\n    ID,NAME\n    ,Synchro+DARK+Effect --  add a some substring logic to trim any trailing /\'s\nfrom\n    (select\n        ID\n        ,NAME\n        --may need to replace max() with min().\n        ,MAX(CASE SPECIALTYPE WHEN "Synchro" THEN SPECIALTYPE +"/" ELSE "" END) Synchro\n        ,MAX(CASE SPECIALTYPE WHEN "DARK" THEN SPECIALTYPE +"/" ELSE "" END) DARK\n        ,MAX(CASE SPECIALTYPE WHEN "Effect" THEN SPECIALTYPE ELSE "" END) Effect\n    from\n        table\n    group by\n       ID\n       ,NAME) sub1\n</code></pre>\n'}, {'answer_id': 18394, 'author': 'Dillie-O', 'author_id': 71, 'author_profile': 'https://Stackoverflow.com/users/71', 'pm_score': 0, 'selected': False, 'text': "<p>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.</p>\n"}]
['https://Stackoverflow.com/questions/18216', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/71/']