Hi All
I have the below table which i need to transform
| DataObjectId | AttributeDefinitionId | Name | Value |
| 09B13831-6566-415B-858E-86A4E702AF5B | DFC2E406-BBDA-46C8-BD56-1769A3504B86 | 3D Model Confidence | Unconfident |
| 09B13831-6566-415B-858E-86A4E702AF5B | E88020E0-7F43-460D-BB71-E1FA7568DD59 | 3D Model File | MDM SQL Test.dwg |
| 09B13831-6566-415B-858E-86A4E702AF5B | 6D33DECE-0898-483A-8222-5F375B266AEC | 3D Model Revision | |
| 09B13831-6566-415B-858E-86A4E702AF5B | 503C84D4-976E-4288-8CC4-3CA88AA3AEDF | AD4 Asset Status | Design |
| 09B13831-6566-415B-858E-86A4E702AF5B | 4791AD8E-F899-4BDB-B2B7-4D6AE4C36693 | Additional Asset Information | |
| 09B13831-6566-415B-858E-86A4E702AF5B | 0C3F7BA3-FBB7-483F-82D6-446A97B0AA10 | Angle | INCLINED |
| 09B13831-6566-415B-858E-86A4E702AF5B | 47941AD2-6D90-471C-94E5-68B82A076F86 | Asset Location Text | |
| 09B13831-6566-415B-858E-86A4E702AF5B | 37EA8051-7A1F-4ACA-B635-F95E80A9A42F | Class ID | GTVE |
| 09B13831-6566-415B-858E-86A4E702AF5B | 79B9B4DA-0C1D-44F5-A5ED-DBF78C86978B | Current Maintenance Contract | RTMC South East |
| 17480D19-86C8-4233-ADC3-63A677309AD6 | DFC2E406-BBDA-46C8-BD56-1769A3504B86 | 3D Model Confidence | Unconfident |
| 17480D19-86C8-4233-ADC3-63A677309AD6 | E88020E0-7F43-460D-BB71-E1FA7568DD59 | 3D Model File | MDM SQL Test.dwg |
| 17480D19-86C8-4233-ADC3-63A677309AD6 | 6D33DECE-0898-483A-8222-5F375B266AEC | 3D Model Revision | |
| 17480D19-86C8-4233-ADC3-63A677309AD6 | 503C84D4-976E-4288-8CC4-3CA88AA3AEDF | AD4 Asset Status | Design |
| 17480D19-86C8-4233-ADC3-63A677309AD6 | 4791AD8E-F899-4BDB-B2B7-4D6AE4C36693 | Additional Asset Information | |
| 17480D19-86C8-4233-ADC3-63A677309AD6 | 47941AD2-6D90-471C-94E5-68B82A076F86 | Asset Location Text | |
| 17480D19-86C8-4233-ADC3-63A677309AD6 | 0B750D72-D592-41C0-9783-35961C99A5F2 | Class ID | LSTR |
| 17480D19-86C8-4233-ADC3-63A677309AD6 | 79B9B4DA-0C1D-44F5-A5ED-DBF78C86978B | Current Maintenance Contract | RTMC South East |
The DataObjectId column contains the Id of a "DataObject". Each data object has attributes associated with it, and each row in the table above contains the name of one of the attributes and its value.
In the example table ive given above, there are two DataObjectId's listed, and each DataObject has the same 9 attributes associated with it.
I want to transform the table so it ends up looking like this:
| DataObjectId | 3D Model Confidence | 3D Model File | 3D Model Revision | AD4 Asset Status | Additional Asset Information | Angle | Asset Location Text | Class ID | Current Maintenance Contract |
| 09B13831-6566-415B-858E-86A4E702AF5B | Unconfident | MDM SQL Test.dwg | | Design | | INCLINED | GTVE | RTMC South East |
| 17480D19-86C8-4233-ADC3-63A677309AD6 | Unconfident | MDM SQL Test.dwg | | Design | | | LSTR | RTMC South East |
The attribute names should become the column names. Each unique DataObjectId should be on its own row and the values for each attribute stored against the correct DataObject and in the column of the associated attribute name.
Can anyone help us with the steps I need to undertake to get the table in this format?
I want the M query code to be as dynamic as possible, so I don't want the attribute names to end up being hardcoded into any M query statement. The reason is that the first table I show above is first filtered to show DataObjectId's which are of the same "asset type" (i can switch what asset type it filters for by changing a parameter). If the attribute names end up being hardcoded into the M query statements, the query is likely to break when I switch to a different asset type which has a different set of attributes associated with it.
