SQL pivot

[RusDavies]

Hello.

In a EAP repository, I have a bunch of TOGAF stakeholders defined.  Each has various tagged values.   I want to make a nice little ModelView for a dashboard out of that information.

So, I've started with a query, as follows:

Code: [Select]

SELECT o.Object_ID, o.Name, tag.Property, tag.Value
FROM (t_object o INNER JOIN t_objectproperties tag ON o.Object_ID = tag.Object_ID)
WHERE o.Stereotype = "Stakeholder"
AND tag.Property IN ("Class", "CurrentCommitment", "LevelOfInterest", "Power", "RequiredSupport")

That gets me a list of properties vs stakeholders, with one property per row, something like this:

Object_ID	Name	Property	Value
123	CEO	Class	High
123	CEO	Power	Keep Satisfied
123	CEO	LevelOfInterest	High
124	CIO	Class	High
124	CIO	Power	Keep Satisfied
124	CIO	LevelOfInterest	High

But what I want is one row per stakeholder, like this:

Object_ID	Name	Class	Power	LevelOfInterest
123	CEO	High	Keep Satisfied	High
124	CIO	High	Keep Satisfied	High

So, clearly, I need to pivot the data.  Doing so *should* be trivial.

Various errors received when goofing around suggest EA to be using the Microsoft Jet Database Engine under the hood.  Therefore, this is how I expect to pivot the data:

Code: [Select]

TRANSFORM First(tag.Value)
SELECT o.Object_ID, o.Name, tag.Property, tag.Value
FROM (t_object o INNER JOIN t_objectproperties tag ON o.Object_ID = tag.Object_ID)
WHERE o.Stereotype = "Stakeholder"
AND tag.Property IN ("Class", "CurrentCommitment", "LevelOfInterest", "Power", "RequiredSupport")
GROUP BY o.Object_ID, o.Name
PIVOT tag.Property

But, when I run this query, the result is nothing.  Nada.  I don't receive any errors, yet it doesn't do anything.  In fact, it doesn't even clear the output from any previous successful query. 

So, what am I doing wrong?  Is my SQL way off?  Or is EA limited in some way? And in either case, why no error?

UPDATE: I see from the "Inside Enterprise Architect" book sample from LeanPub, that "[The SQL Editor window] accepts only SELECT statements. Any other SQL (like e.g. UPDATE) is silently ignored!".   Is the issue then that my query begins with TRANSFORM, rather than SELECT? 

UPDATE 2:   I was proofing my SQL via the SQL Scratch Pad of the Project Search Tool, where it could be saved without error but failed to run.  However, if the same query is used in the source for an actual ModelView, then saving the query generates the error "Unable to save Model View Data, invalid Custom SQL. Only Select statements are supported."   It seems then that the issue is indeed the use of the Transform construct, (together with EA being inconsistent about error reporting).   Therefore, how does one perform a pivot in EA?  Does this mean I now have to create (maintain/distribute/etc) a whole Search-Addin to perform what should be a trivial pivot?

[Geert Bellekens]

Hi Rus,

You can do so by joining the same table multiple times.
Something like this:

Code: [Select]

SELECT o.ea_guid AS CLASSGUID, o.Object_Type AS CLASSTYPE, o.Object_ID, o.Name,
clTag.Value AS Class, ccTag.Value AS CurrentCommitment, liTag.Value as LevelOfInterest, pTag.Value as Power, rsTag.Value as RequiredSupport
FROM (((((t_object o
INNER JOIN t_objectproperties clTag ON ( o.Object_ID = clTag.Object_ID
and clTag.Property = 'Class'))
inner join t_objectproperties ccTag on ( o.Object_ID = ccTag.Object_ID
and ccTag.Property = 'CurrentCommitment'))
inner join t_objectproperties liTag on ( o.Object_ID = liTag.Object_ID
and liTag.Property = 'LevelOfInterest'))
inner join t_objectproperties pTag on ( o.Object_ID = pTag.Object_ID
and pTag.Property = 'Power'))
inner join t_objectproperties rsTag on ( o.Object_ID = rsTag.Object_ID
and rsTag.Property = 'RequiredSupport'))
WHERE o.Stereotype = 'Stakeholder'
Geert

[RusDavies]

Ho ho!  Yeap, that's what I needed, perfect.  Thanks Geert.