Merge data object table with associated attributes table in a view

Question

Here's the setup: I have several tables that hold information for data objects which have the potential to have various and sundry bits of data associated with them. Each of these tables has an associated attributes table, which holds 3 bits of information:

1. the id (integer) of the row the attribute is associated with
2. a short attribute name ( < 50 chars )
3. a value (varchar)

The object table will have any number of columns of varying data types, but will always have an integer primary key. If possible, I would like to set up a view that will allow me to select a row from the object table, and all of its associated attributes at one go.

*EDIT*

Ideally, the form I'd like this to take is having columns in the view with the names of the matched attribute from the attributes table, and the value as the value of the attribute.

So for example, if I have table Foo with columns 'Bar', 'Bat', and 'Baz' the view would have those columns, and additionally, columns for any attributes that a row might have.

*END EDIT*

Now, I know (or think I do) that SQL doesn't allow using variables as an alias for a column name. Is there a clean, practical way of doing what I want, or am I chasing a pipe dream?

The obvious solution is to handle all of this in the application code, but I'm curious if it can be done in SQL.

Answer 1

It sounds like you want a view for each of your 'object' tables as well as its 'attributes' table. Correct me if I am wrong in my reading. It's not clear what your intentions are with 'using variables as an alias for a column name'. Were you hoping to merge ALL your objects, with their different columns, into one view?

Suggest create one view per entity table, and join to its relevant 'attributes' table.

Question though - why is there one matching attributes table for each entity table? Why are they split out? Perhaps you've made the question simpler or obfuscated, so perhaps my question is rhetorical.

  CREATE VIEW Foo AS 

       SELECT O.ID
              ,O.EverythingElse
              ,A.ShortName
              ,A.SomeVarcharValue
       FROM 
              ObjectTable AS O  --customer, invoice, whathaveyou
       INNER JOIN 
              ObjectAttribute AS A ON A.ObjectID = O.ID

To consume from this, you could:

SELECT * FROM Foo WHERE ID = 4 OR SELECT * FROM Foo WHERE ShortName = 'Ender'

Answer 2

The answer depends on what you are actually seeking. Will the output of the view have one row per attribute per object or one column per attribute per object? If the former, then I'm not sure why you need a view:

Select ...
From ObjectTable
    Join AttributeTable
        On AttributeTable.Id = ObjectTable.Id

However, I suspect what you want is the later or something like:

Select ...
    , ... As Attribute1
    , ... As Attribute2
    , ... As Attribute3
    ...
From ObjectTable

In this scenario, the columns that would be generated are not known at execution because the attribute names are dynamic. This is commonly known as a dynamic crosstab. In general, the SQL language is not designed for dynamic column generation. The only way to do this in T-SQL is to use some fugly dynamic SQL. Thus, it is better done in a reporting tool or in middle-tier code.