Pivot Identity Data?

[former_member2987]

I'm wondering anyone knows a SQL based method of pivoting the information in the Identity Store (as held in say, idmv_value_ext_active) to a more usable format. 

I've looked at the SQL Server pivot command but it does not seem to apply.

The only other way I can think of to do this is to virtualize the Identity Store via VDS and then read the data in a From LDAP pass, but I'd prefer to do this without the extra moving parts...

Thanks for your help!

Matt

[former_member2987 · Accepted solution]

Good information on this can be found in this blog entry:

[Former Member]

Hi Matt,

I know what you mean but Pivot is hard to get right as you need to fake an aggregate function, you might be better off using CASE such as a pending query I created recently:

DECLARE @username varchar(12)

SET @username = 'V023902'

SELECT

    mskey,

          @username AS [User],

    MAX(

        CASE attrname

            WHEN 'MX_ENTRY_REFERENCE' THEN searchvalue ELSE NULL

        END

    ) AS MX_ENTRY_REFERENCE,

          MAX(

        CASE attrname

            WHEN 'MX_ATTRIBUTE_NAME' THEN searchvalue ELSE NULL

        END

    ) AS MX_ATTRIBUTE_NAME,

          MAX(

        CASE attrname

            WHEN 'MX_ATTRIBUTE_VALUE' THEN searchvalue ELSE NULL

        END

    ) AS MX_ATTRIBUTE_VALUE,

          MAX(

        CASE attrname

            WHEN 'MX_OPERATION' THEN searchvalue ELSE NULL

        END

    ) AS MX_OPERATION,

          MAX(

        CASE attrname

            WHEN 'MX_VALIDFROM' THEN searchvalue ELSE NULL

        END

    ) AS MX_VALIDFROM, 

          MAX(

        CASE attrname

            WHEN 'MX_VALIDTO' THEN searchvalue ELSE NULL

        END

    ) AS MX_VALIDTO

FROM (SELECT mskey, attrname, searchvalue FROM mxiv_sentries WHERE mskey IN

          (select mskey from mxiv_sentries where attrname = 'MX_ENTRY_REFERENCE' AND searchValue LIKE (

                    select distinct mskey from mxiv_sentries where searchvalue = @username))) pt

group by mskey

Each of the case values becomes a column header so for the user I get the details of each mskey object on one line.

Heath

[Former Member]

Hi,

We have quite a few custom reports we've created, but we did not do a "pivot".  Not sure what that is.  If you describe the type of report that you're looking for, I can help with the outline of the sql query.

[former_member2987]

Chris,

If you recall the look of the idmv_value* tables, they are basically identified one attribute/row with MSKEY being the attribute that "links" all of the attributes together.

What I would like to do is "pivot" that data so that there is one row listing all of the attributes with MSKEYVALUE as the identifier for each row. 

Does this make sense?

Thanks...

PS -- I did look into reporting, but it seems the only supported reporting now is CRW based and my client is not currently interested in going this way for a solution.  They would prefer it be "in house"

Thanks

[Former Member]

The only way I would know how to do it would be to write a stored procedure similar to the following:

select
i1.MSKEY,
i1.SearchValue as MSKEYVALUE,
i2.SearchValue as DISPLAYNAME,
i3.SearchValue as MX_ENTRYTYPE,
i4.SearchValue as MX_FIRSTNAME
from
idmv_value_basic_active i1,
idmv_value_basic_active i2,
idmv_value_basic_active i3,
idmv_value_basic_active i4
where
i1.AttrName = 'MSKEYVALUE' and i1.MSKEY = i2.MSKEY and
i2.AttrName = 'DISPLAYNAME' and i2.MSKEY = i3.MSKEY and
i3.AttrName = 'MX_ENTRYTYPE' and i3.MSKEY = i4.MSKEY and
i4.AttrName = 'MX_LASTNAME'

It's time consuming but it'd work.  To make the performance more efficient you could query mxi_entry directly rathre than the view.