hi all
My database is based around some generic "entities". I can set generic properties on these entities, using a second properties table. The pertinent parts of the tables look like this:
Entities:
ID (int primary key)
Name
Properties:
ID (int primary key)
Name
Value
Entity
I'm trying to create a view I can pass directly to a UI. I want to query for a list of particular property names, and have those appear as COLUMNS in the result, with the values underneath.
E.g if our entities are cars, the properties might be colour, engine size, number of doors.
I need the properties table to remain as it is - it has to be a completely generic system. I want my query to give me result that looks like:
Code:
EntityName | Colour | EngineSize | NumberOfDoors
-----------------------------------------------------------------------
Car1 | Blue | 3.2L | 2
Car2 | Silver |
Car3 | Green | 1.8L | 5
the best I can do at the moment looks like:
Code:
EntityName | Property| Value
-----------------------------------------------------------------------
Car1 | Colour | Blue
Car1 | EngineSize | 3.2L
Car1 | NumberOfDoors | 2
Car2 | Colour | Silver
...etc
As you can see some entities may not have certain (or any) properties set. I'd like to show all entities regardless.
To top it all off, I am using sqlite so am missing some more advanced sql functionality.
I would really appreciate your help, even if it's just telling me I can't do this with sql. I can after all set up this data using multiple queries and my application code, but it will keep things cleaner if I can do it in one sql statement.
Many thanks in advance
dBforums
>
Rearrange data in query- not sure I can do this??
04-08-10, 05:36
Not without dynamic SQL anyway.
Linear Mode
