Show Tagged Values within its own column in model searches

One often asked question is, how to show each tagged value in its own column in model searches.
Tagged Values are stored in the table t_objectproperties. We can now use the table t_object,
in which all model elements are stored and combine it with the table t_objectproperties to get all tagged values for each model element.

SELECT t_object.Name, t_objectproperties.Property, t_objectproperties.Value 
FROM t_object 
LEFT OUTER JOIN t_objectproperties ON t_object.Object_ID = t_objectproperties.Object_ID

However, unfortunately,  we always get a column for the tag name and one for the tag value and this for each tagged value of a model element.
So we get a set of duplicated rows that differ only by their tag name or tag value.

We would like to have a separate column for each tag name and if the model element has a value for this tag name, this should be displayed.
What we can do now is to look for all model elements and search for predefined tag names that we want to output as our own columns.

SELECT 
t_object.Object_ID,
t_object.Name, 
a.Value AS myA, //gib eine Spalte mit Namen myA aus und schreibe den Wert des Tags aus der Menge a hinein.
b.Value AS myB  //gib eine Spalte mit Namen myB aus und schreibe den Wert des Tags aus der Menge b hinein.
FROM (t_object 
LEFT OUTER JOIN t_objectproperties AS a ON (t_object.Object_ID = a.Object_ID AND a.Property = 'myA')) // alle Tagged Values des Modell-Elementes mit dem Namen myA 
LEFT OUTER JOIN t_objectproperties AS b ON (t_object.Object_ID = b.Object_ID AND b.Property = 'myB')  // alle Tagged Values des Modell-Element mit dem Namen myB

With this approach we can only show predefined tagged values and not all of them, but we get a column for each tag name.
If we add the following lines in the projection, the TaggedValue View will display all other tags of the selected model element in the result table.

SELECT
t_object.ea_guid AS CLASSGUID,
t_object.Object_Type AS CLASSTYPE,

Posted in Model Search
Tags: