"KBinderA" wrote:

> I have been using MS Access Crosstab queries.  The nature of this query is a
> bit different then most of the examples I have been seeing.  I don't need to
> do any math or joins.  I basically have a denormalized table containing
> metadata and instance data. 
>
> For example:
> The column for my "Objects" table are:
> -ObjectMetaType  VARCHAR (255)
> -ObjectInstance  VARCHAR (255)
> -ObjectInstanceID  VARCHAR (255)
> -ObjectMetaProperty  VARCHAR (255)
> -ObjectPropertyValue  NTEXT
> ===================================
> Example records:
> Application, My_App_1, 123, Description_Prop, "Some Descriptive Text"
> Application, My_App_1, 123, Version_Prop, "Version 1.0"
>
> Note:  The number of "ObjectMetaProperty" entries for a given
> "ObjectMetaType" is configurable so we can not hard code those into a query.
>
> I want this output:
> HEADER:  ObjectMetaType,ObjectInstance,ObjectInstanceID
> Description_Prop,Version_Prop
> VALUE: My_App_1, 123,"Some Descriptive Text","Version 1.0"
>
> I can write the following crosstab query in MS Access to produce this:
>
> TRANSFORM Last([Objects].ObjectPropertyValue) AS LastOfvalue
> SELECT [Objects].ObjectMetaType, [Objects].ObjectInstance,
> [Objects].ObjectInstanceID
> FROM [Objects]
> GROUP BY [Objects].ObjectMetaType, [Objects].ObjectInstance,
> [Objects].ObjectInstanceID
> PIVOT [Objects].ObjectMetaProperty;
>
> At the moment, I have only SQL Server 2000.  I do not have any OLAP
> capabilities in this particular install.  Is there any way to achieve this
> with straight SQL?  Any SP's out there that can handle this.  I have tried
> those referenced in this newsgroup and have had no success.