Pivoting the Livelink LLATTRDATA Table

For those who have tried to pull attribute data, you know that each attribute value is on a separate row and may even be in a different column. Here's a query (sorry MSSQL guys) that will put all the attributes into one row when you run it. This can be used in web reports or external apps to grab category/attribute data and display it nicely. Good luck.

select lla.id
, max(decode(lla.attrid, 2, lla.valstr, null)) "Attribute Name 1"
, max(decode(lla.attrid, 3, lla.valstr, null)) "Attribute Name 2"
, max(decode(lla.attrid, 4, lla.valstr, null)) "Attribute Name 3"
, max(decode(lla.attrid, 5, lla.valstr, null)) "Attribute Name 4" 

, max(decode(lla.attrid, 6, lla.valstr, null)) "Attribute Name 5"

, max(decode(lla2.attrid, 5, lla2.valstr, null)) "Attribute Name 6"

from llattrdata lla, llattrdata lla2
where lla.id = lla2.id
and lla.defid = 47881102 --category id 1
and lla2.defid = 40278096 --category id 2
and lla.id = 48212327 --can replace with a subselect to get a group of dataIDs in dtree (ex: *where defid in (select dataid…))
and lla2.id = 48212327
group by lla.id

Please note that the defids and ids will not be the same in your instance. You need to replace them with your own values. 

Here's the explanation:

• select lla.id - Pulls the Livelink object ID from the LLATTRDATA table
• , max(decode(lla.attrid, 2, lla.valstr, null)) "Attribute Name 1" - This gets only one row, one column for the specific attribute. What you need to change: The numeric value inside the decode, right after the "lla.attrid". The "2" in this case represents the attribute ID. This number can be found on the CATREGIONMAP table as the number found suffixed to the value in column REGIONNAME (Attr_1234567_###). This number gets used in the decode statement to filter against the ATTRID column in table LLATTRDATA. 
• , max(decode(lla.attrid, 3, lla.valstr, null)) "Attribute Name 2" - SAME AS ABOVE
• , max(decode(lla.attrid, 4, lla.valstr, null)) "Attribute Name 3" - SAME AS ABOVE
  , max(decode(lla.attrid, 5, lla.valstr, null)) "Attribute Name 4" - SAME AS ABOVE
• , max(decode(lla.attrid, 6, lla.valstr, null)) "Attribute Name 5" - SAME AS ABOVE
• , max(decode(lla2.attrid, 5, lla2.valstr, null)) "Attribute Name 6" - In this line, I am referencing an attribute ID off another category, hence the lla2.attrid.
• from llattrdata lla, llattrdata lla2 - Datasource for pulling the attribute values. You will need to call the LLATTRDATA table for each different category you are trying to grab data from. In this case, there are only two that are associated with the Livelink objects in question.
• where lla.id = lla2.id - The inner join to connect the two LLATTRDATA tables. If you had three LLATTRDATA calls, then the second JOIN would be "and lla2.id = lla3.id"
• and lla.defid = 47881102 --category id 1 - The filter for the first LLATTRDATA table call, i.e. the DEFID or the category ID, which can be pulled from the CATREGIONMAP table.
• and lla2.defid = 40278096 --category id 2 - Same as above except this is calling the defid of the second category
• and lla.id = 48212327 - Filter to get a specific Livelink object in the LLATTRDATA table. This value is the object ID. You can actually implement a subselect statement in place of the hardcoded dataid. This will allow you to get multiple objects with their category/attribute values associated.
• and lla2.id = 48212327 - Same as above. You have to apply the same filter to all LLATTRDATA table calls because of the table joins and pivots.
• group by lla.id - Forces the results to show up in one row per Livelink object

Usages of this query: 

• Faster than joining multiple select statements to get LiveLink attribute data.
• Web Reporting... I've actually used this as a data source for a web report that returns 30K+ records. If you implement the SQL as a materialized view in the LiveLink schema and  reference it in your LiveReport SQL, then the results should be quick. 
• Quick go-to for data pulling that clients need.